Hello, I am trying to read a file "xlsx" from ASP, the problem is when I try to delete the file, ASP inform not have permits to delete it, it is as if the connection to file not closing properly, the code is:
Code Snippet
if extension = "xlsx" or extension = "xls" then 'si el archivo es excel
if extension = "xls" then
sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&sFilePath&";DefaultDir="&sDataDir&";"
else
sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=TRUE;DBQ="&sFilePath&";DefaultDir="&sDataDir&";"
end if
sFileSQL = "SELECT * FROM [" & sSheetName & "$]"
set oFileCN = server.createobject("ADODB.Connection") 'creo objeto adodb
oFileCN.Open sFileConnectionString 'abro el exel
set oFileRS = oFileCN.Execute(sFileSQL) 'selecciono los registros
if not oFileRS.EOF then 'si es distinto de EOF obtengo un array con los valores de las columnas
aSourceData = oFileRS.getRows()
end if
oFileRS.Close
set oFileRS = nothing
oFileCN.Close
set oFileCN = nothing
'asigno los mails a la variabls "readDatafile"
readDatafile = aSourceData
and the error
Microsoft VBScript runtime error '800a0046'
Permission denied
/gestion/edit_import_db_mail.asp, line 27
Any ideas?, Greetings and forgiveness for my English
I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).
I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.
This is the sample code we are working with:
SELECT X.MEMBID FROM OPENROWSET( 'MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx', 'SELECT * FROM [Sheet1$]' ) AS X
I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?
But if I RDC into TEST1, I cannot run the query. I get this error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.". OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.". OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.
I can run the query as SA from TEST1.
And of course, the developer can't run it either.
And it works fine in the production server.
I'm thinking the basics are there but something isn't right in some permission somewhere.
Actually, I am working in a leading advertisement company where we were handling all of our data in Access, but because our database is getting really large, we have to shift to SQL because queries were taking long time to execute. I have no idea what SQL is. However, i have a problem. In access, i had to import an xlsx(excel 2007 file) in my database. In access I used to do this:
Sub SendTableToExcel() DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel9, "Campaigns", _ "C:frequentstuffsCampaigns.xlsx", True End Sub
The format(ie the col Names and structure) in table Campaign and Excel File, is same. I just need to copy the data from excel into SQL. How can i do the same thing in SQL? To be very precise, I am novice to SQL so if explained in a novice languauge, i would really appreciate it!!
Vikas B
Vikas Bhandari Visit My Blog www.excelnoob.blogspot.com
I uploaded an xlsx file to my reporting services. When i click on the xlsx file link in the reporting services frontend, a internet explorer window is opened with the xlsx file information (reporting services) and the xlsx file is opened by the excel application.
Is there any way to prevent the Internet explorer window to be opened?
I have a job on SQL server agent on SSMS that runs daily, however, where the results go as the query wont appear on the screen.I want to have it automatically save the results from the query to a .csv or an. xlsx file in a specified folder.
I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?
I have a very simply package using an Excel connection to an XLSX file. It's a straight read of the file and import onto a table.Â
The package works fine in Visual Studio 2008 development and also runs fine when executing on the (server I copied it to) under Integration Services. Â Â
However, under a SQL Agent, the package (32-bit is checked) can not acquire the connection to an excel file.  I use UNC pathing to the file. I've read other posts about similar problems and tried various scheduling options (including Owner of job).  Â
I even tried a to trigger it with a command-line which did not work:Â
"C:Program Files (x86)Microsoft SQL Server100DTSBinnDTEXEC.exe" /sq "our packagesMy_XLSX_File_Import" /SERVER myserver /X86Â /CHECKPOINTING OFF /REPORTING E
All errors are:  "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302."Â
I have been strunggling to find solution to convert XLSX files with multiple sheets to csv file.
Requirements >>Â Convert XLSX file with multiple sheets to CSV file >>Â CSV file names : XLSX filename + '_' + sheet name >>Â scirpt has to be in VB as i am using ssis 2005 >> I started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task. >> found web link as useful.. [URL] ....
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'> <a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File </a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
Hello Experts, I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle). PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not.. What should go here ? ->Under Class A
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?
Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.
I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...
I am trying to load DBF files into SQL server within CLR (actually if just running the select statement outside, say within the SQLQuery window, i got the same result), but with the following error:
Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "VFPOLEDB" for linked server "MYDBF" does not contain the table "T8866064". The table either does not exist or the current user does not have permissions on that table.
I was wondering on a question that : exporting a report to xlsx is feature of SSRS server or of Report viewer control itself.I have multiple report servers (eg 2008 R2, 2012 and 2014) . I am using a windows application and reportviewer version 9.0.So when I use report server 2008 R2 its not showing the xlsx export option but working fine with other report servers.Again I changed the control to 11.0 (also 12.0) and checked the same scenario and same results with Reporting server 2008 R2 but working fine with 2012 and 2014. if its worth to change the Report Viewer control or just changing the server is okay to achieve the functionality of exporting to xlsx.
Need help reading a binary file see below for details...
I have uploaded a csv file into a sql table. Now i want to extract the data and insert the data in the csv file into another sql table. What commands can i use in sql to extract/ read the data ?
Hi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr")) Dim Cm As SqlCommand Dim dr As SqlDataReader Dim FileName As String = Guid.NewGuid.ToString & ".csv" Dim FilePath As String = Server.MapPath("") & "CSVFiles" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write) Dim sw As StreamWriter = New StreamWriter(fs) Dim Line As String Dim lItem As System.Xml.XmlElement Dim i As Int16 If ValidatePage() Then Cm = New SqlCommand(BuildQuery, Conn) Conn.Open() dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument() lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes Select Case lItem.InnerText Case "chkPRId" Line &= "PR Id," Case "chkDateIssued" Line &= "Date Issued," Case "chkOriginator" Line &= "Originator," Case "chkBuyer" Line &= "Buyer," Case "chkVendor" Line &= "Vendor," Case "chkCostCode" Line &= "Cost Code," Case "chkOracleRef" Line &= "Oracle Reference," Case "chkTotal" Line &= "Total," End Select Next Line = Line.Substring(0, Line.Length - 1) sw.WriteLine(Line)
Line = "" While dr.Read For i = 0 To dr.FieldCount - 1 Line = Line & dr(i) & "," Next sw.WriteLine(Line) Line = "" End While
dr.Close() Conn.Close() sw.Close() fs.Close() Response.Redirect(FilePath) End If ResetPage() PopulateListBoxFromXML()
Hi, I received a SQL Server 6.5 database (.dat and .Log files). I'm currently running 7.0. Is there any way to read/import this data? I tried to do this: I have installed SQL Server 6.5. And After install, I have created a database with the same names (.dat an .log) and make it bigger than the first .dat and .log files. Then, I have stoped the SQL server service, have deleted the .DAT and .LOG files that i have created, and have copied the first .DAT and .LOG in their place. I have Started the SQL server service and the database was beeing suspected. I stil unable to read data. Can you help me?
How can I read one external file (*.txt,*.csv) through stored procedure in MS Sql Server 2000 ?
On the database Interbase its very simple :
[CREATE TABLE table [EXTERNAL [FILE] ’ <filespec>’] ( <col_def> [, <col_def> | <tconstraint> …]); EXTERNAL [FILE]“<filespec> ” Declares that data for the table under creation resides in a table or file outside the database; <filespec> is the complete file specification of the external file or table]
I am programming to read read sqlserver log file without shutdowning sqlserver. However it is said that the file is used by another process which I believe is sqlserver. Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.
patelnimisha writes "i am try to read three xml files and transfer its data into sql databse. so i have no any idea about this qurey. give me coding for this query"