Automation Error Sql Server
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn sql server automation ideas more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags sql server automation scripts Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you,
Automation In Sql Server 2008
helping each other. Join them; it only takes a minute: Sign up Automation Error when executing SQL Server Script in Excel VBA up vote 0 down vote favorite I am opening an SQL Server Connection in EXCEL VBA
Ole Automation Sql Server
and on the objMyCmd.Execute line when it is using the SQL script I am getting this error message: "Run-time error '-2147217900 (80040e14)') Automation error" I have reviewed other SO posts that seem to reference an issue with the connection string itself, but I don't believe that is the issue as I am able to pull the first few variables listed when eliminating the rest of the SQL script. I have attempted to review the SQL code to sql server ole automation security risks see if I am using an incorrect format, or if the language is not written properly and I am not able to determine the issue. I am hoping with some Q & A we may notice something I have missed in how this is written? Please let me know if there is additional information I can provide, below is the code up to the point of error. Sub SQL_GetAgentChart() Dim dtDate As Date Dim myTable As ListObject Dim DataServer As String Dim Database As String Dim constring As String DataServer = "GLSSQLMADP2" Database = "PERF_MGMT_BWRSRV_PROD" constring = "Driver={SQL Server};Server=" & DataServer & "; Database=" & Database & "; Trusted_Connection=yes" Dim AVStartDate As Date Dim AVEndDate As Date Dim RepID As Long 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset Set myTable = Worksheets("Witness").ListObjects("tblWitness") AVStartDate = DateValue("Mar 01, 2016") AVEndDate = DateValue("Mar 31, 2016") RepID = 2040 'Open Connection' objMyConn.ConnectionString = constring objMyConn.Open 'Set and Excecute SQL Command' Set objMyCmd.ActiveConnection = objMyConn objMyCmd.CommandText = " " & _ "SELECT PERSN_XTRNL_ID_NR, SOURCE, LOGGINGTS, DD7, CUREREASON, CUREDATE, LNSTATUS " & _ "FROM TTB " & _ "WITH INCALL AS (SELECT T.CUREREASON, CUREVALUE " & _ "FROM TTB T " & _ "JOIN PERSONNEL P ON T.PERSONNELID = P.PERSONNELID " & _ "LEFT JOIN CURETRANSLATE C ON T.CUREREASON = C.CUREREASON AND T.L
here for a quick overview of the site Help Center Detailed
Sql Server Ole Automation Enabled
answers to any questions you might have Meta Discuss the sql server ole automation procedures workings and policies of this site About Us Learn more about Stack Overflow the company sql server sftp automation Business Learn more about hiring developers or posting ads with us Super User Questions Tags Users Badges Unanswered Ask Question _ Super User is a http://stackoverflow.com/questions/36340991/automation-error-when-executing-sql-server-script-in-excel-vba question and answer site for computer enthusiasts and power users. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top VBA Automation error when querying database through ODBC up http://superuser.com/questions/987435/vba-automation-error-when-querying-database-through-odbc vote 1 down vote favorite I am trying to run Excel macro to query some data from my ODBC database. My database details are as below: DSN: sgdv UID:l8qc1 Password:l8qc1 server:sgdv I am trying to query the data from the database but the VBA shows the error as below for the line Connection.Open "DSN=sgdv". Run-time error -2147217843 (80040e4d) Automation error I can't identify the error. Please help. Below is my code Sub ReadDB() Dim mainWorkBook As Workbook Dim intRowCounter Set mainWorkBook = ActiveWorkbook intRowCounter = 2 mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear Set Connection = CreateObject("ADODB.Connection") Connection.Open "DSN=sgdv" strQuery = "SELECT * FROM DEDICT01 where DEDICT01.SER_SN='Z1E80R4C'" Set resultSet = Connection.Execute(strQuery) Do While Not resultSet.EOF mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("DEDICT01.CUST_PARTS_NO").Value resultSet.movenext Loop resultSet.Close End Sub I have modified the program and my new coding is Sub ReadDB() Dim mainWorkBook As Workbook Dim intRowCounter Set mainWorkBook = ActiveWorkbook intRowCounter = 2 mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear Set Connection = CreateObject("ADODB.Connect
VBA Code & Other Help SQL sql excel vba connection problem If this is your first visit, be sure to check out the FAQ by clicking the link above. http://www.vbaexpress.com/forum/showthread.php?40787-sql-excel-vba-connection-problem You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 9 of 9 Thread: sql excel vba connection problem Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode sql server Switch to Threaded Mode 02-05-2012,07:47 AM #1 alex1404 View Profile View Forum Posts View Blog Entries View Articles VBAX Regular Joined Aug 2011 Posts 8 Location sql excel vba connection problem hi all, when i run this code, i would get "run time error -2147467259 automation error unspecified error" i used debug, and the error accord in oCon.Open please sql server ole help me. Sub Connect2SQLXpress() Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset Set oCon = New ADODB.Connection oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;" oCon.Open Set oRS = New ADODB.Recordset oRS.ActiveConnection = oCon oRS.Source = "Select * From Table1" oRS.Open Range("A1").CopyFromRecordset oRS oRS.Close oCon.Close If Not oRS Is Nothing Then Set oRS = Nothing If Not oCon Is Nothing Then Set oCon = Nothing End Sub Formatting tags added by mark007 Last edited by xld; 02-07-2012 at 02:37 AM. Reason: Added VBA tags Reply With Quote 02-06-2012,04:30 AM #2 mohanvijay View Profile View Forum Posts View Blog Entries View Articles VBAX Tutor Joined Aug 2010 Location MADURAI Posts 268 Location Try different connection strings like below (Which works fine for me) "Provider=SQLNCLI;Server=YourServerName;Database=YourDataBaseName;Uid=UserName;Pwd=Password;" Formatting tags added by mark007 for more connection strings see http://www.connectionstrings.com/ Reply With Quote 02-06-2012,06:46 AM #3 Norie View Profile View Forum Posts View Blog Entries View Articles VBAX Master Joined Jan 2005 Posts 1,829 Location Are you sure the server name is right? Does it definitely hav ./ at the start? Reply With Quote 02-10-2012,