I'm not sure this is the right forum for this thread, but hopefully the Admins can move it if it isnt.
Here is my situation.
I had a web server with a web page that referenced an Excel spreadsheet.
(a href="c:webservernamexyz.xls" target=main)
there is a query to retreive data in the ss from a Access Database.
I had to move the website from one server to another, which included moving the data from an Access Database to a SQL 2000 Database, which sits on a SQL Server.
There is a DSN created on the web server for the connection.
From the web server, I can open the Excel Sheet and it queries the database fine.
My issue is that now when someone hits the webpage from their computer, they get the spreadsheet, but since they dont have a DSN on thier machine, the get an error stating "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified."
Is there a way to specify to use the DSN on the web server so I wouldnt have to create a DSN on every machine that would need to access it?
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'. -The name of the workbook is 'Revenue_to_All_Accounts.xls' -Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS -Provider; Microsoft Jet 4.0 OLE DB Provider -Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls -Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query; SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
I set up an Excel spreadsheet for several users that queries a SQL Server. I provided the spreadsheet to the users and set it to refresh when ever it is open. The query is working fine. The problem is the users are prompted with a SQL Server login screen every time they open the file. The login screen shows the server name, the username and the password. All they have to do is press 'OK' and the screen goes away. Why are they being prompted with this screen? What can I do to eliminate the users from even seeing this window?
I also receive this window whenever I open the file and I created it on the workstation where i'm opening it.
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
What: I am trying to import data from spreadsheets to SQL Server.
Where: Windows Vista, SQL Server 2005 Express, Office 2007.
How: Using linked servers following KB306397.
In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:
Linked server: XLSX
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: XLSX
Data source: D:XLSX.xlsx
Provider string: Excel 12.0 The linked server was created ok.
I then followed KB321686 and ran this:
select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')
and got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')
the error is:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.
It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.
From the same KB article, I also tried:
select * from XLSX...[Sheet1$]
but got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select * from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server. (http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
Hello, I am new to this sql server reporting server technology. I have a requirement like the table header should repeat in all pages of the exported excel's print preview. So I have created the header columns with fixed lenths as same as table's header in the page header of the report. Then only the Header will repeat in all pages of the print preview.
But the problem is when doing like this, many of the columns have been merged together. The report layout is ok. But cant sort the data. It says a message "This operation requires the merged cells to be identically sized."
In some forms i have found the header control's edges need to be sized identically with the tables' columns edges. I did it. But still the columns are merged when exporting.
i have made some Data Mining Model Examples in Excel 2007 (not temporarily!). They where there after leaving an re-opening Excel. I have used them several times. Then I want to look, if I can see them also via SQL Server Managment Studio in the Analysis Services. There where nothing in the DMAddInDB in Analysis Services.
And after this, in Excel my DataMining Models have disappeared and all Models i have made since this disappeared also.
Perhaps I have destroyed the database. But will this happen every time? Can I share Data Mining Models I have made with Excel with Projects in SQL Server Analysis Services?
I have an sql server table which serves as a criteria table for my sql server query.
i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.
The table in question has the following fields
SELECT [Cluster] ,[Max_Break_btw] ,[RefD_Max_Break] ,[DischD_Max_Break] ,[MaxReviewPeriods] FROM [databseName].[dbo].[SpellClusterAssum]
I will like to change / update the values in the "[Max_Break_btw]" column.
Hi, I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000. The string I need to import is composed by 5 different several blocks and looks like:
The detail of the SQL string is at: http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1 to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet Dim SqlCnt, cmd1, cmd2, cmd3 'set the properties and open a connection
cmd1="use my_db" cmd2="create table mytb" cmd3="insert into mytb"
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
Hi, I am new to ASP.net. I have worked on a VB.net code to export data from sql server to excel. The code is simple and works well. Now I am placing this code in a button click event in a asp.net web project. The code has bugs now and I am not sure how to solve those. Any help is appreciated. Thanks. CODE: Protected Sub btnDumpMaterial_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'ErrorMsg.Show("Not Implemented Yet1") 'lblName.Text = txtLast.Text & ", " & txtFirst.Text Dim DBConnection As String = "Provider=SQLOLEDB.1;uid=sa;password=test ;database=Northwind;DataSource={localhost}" Dim sql As String = "SELECT CustomerId, CompanyName, ContactName From Customers" Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet oExcel = CreateObject("Excel.Application") oExcel.Visible = True oBook = oExcel.Workbooks.Add oSheet = oBook.ActiveSheet Conn.Open(DBConnection, "sa", "test", -1) rs.Open(sql, DBConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1) oSheet.Range("A1").CopyFromRecordset(rs) rs.Close() Conn.Close() End Sub ERROR MESSAGE: Type 'ADODB.Connection' is not defined Type 'ADODB.Recordset' is not defined Type 'Excel.Application' is not defined Type 'Excel.Worksheet' is not defined Name 'ADODB' not declared
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
Is there a way to schedule an 'export from Excel to SQL server'job? When I do that, it pops up with errors saying that the file path is not correct even though it is correct! Thanks1
I am having a problem updating my MS Server database with the code bellow. I think the problem is with this line: conn.ConnectionString = "Provider=SQLServer;
can anybody help?? see also the attached for additional info. here is the code in Excel
Sub INSERTDATAINDB()
Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim AppPath, app As String
AppPath = "c:LOGCALL est_Data.MDF" Set conn = New ADODB.Connection Set cmd = New ADODB.Command
I have users entering data on an excel spreadsheet. I want that data to go to Ms SQl Server (local) database and into a table called logcall_table. How do I do it? More prcisely, how do i do it in detail? I can not find answers on the net and I am really lost. you can also refer to "SQL from Excel to MS SQL Server 7.0" my earlier post on this issue for more detail on how I have been trying to do it.
I need to create a "Master Customer Database" of all our mailing lists. There are about 60-70,000 total contacts in about 25 separate Excel spreadsheets. Most formatted like this: First/Last/Company/Address/City/State/Zip/Phone/List/e-Mail. There are duplicates on different lists, so for example the same name/address might be on 3 of the lists. I want to do away with the 25 separate lists and create one Master database. The Master database must eliminate all duplicate entries, yet still note where the duplicates came from. For example if John Smith was on the Goodyear list and the Michelin list, I only want one record of him, but I need to know he was on both lists for segmenting purposes. The Master list must have this field that shows what list/lists they came from so that they can be segmented and mailed/e-mailed/called, etc... Questions:Do you think in this circumstance it would be better to create an Access Database? SQL database? Can these easily be segmented/filtered and exported to CSV/XLS/Word? I'm just trying to figure out basic structure of the databeses. any suggestion would be greatly appreciated.Thanks
I am trying to upload a excel spreadsheet using a web application application into a sql server database.
Basically I'm tryign to code a a upload button, that takes the excel spreadsheet and inserts it into a table in the database.
I have been lookign for code examples but cannot find out, and I am really struggling...any help would be greatly appreciated. I am trying to do this using asp.net (vb).
I posted this earlier, but it did not seem to get any views. Please help!
I'm trying to import an excel spreadsheet into SQL Server. I keep getting an error saying that 2 columns cannot except NULL values. Here's the setup of each file.
SQL TBL: My table has 9 columns. the first column is a Primary Key and IDENTITY(1,1). My last column is a dateCreated column with a default of getDate(). Both values are set to NOT NULL. The 7 inside columns are just plain varChar datatypes.
XLS: My spreadsheet has 7 columns which corelate to the 7 inside columns of my SQL TBL. I do not want to specify my first and last columns because they should be unique to the SQL TBL (identity and getDate()).
Any help with how to do this would be great. If you need more info, please let me know.
I have an Excel worksheet with 4 columns:F1 F2 F3 AutoNoA Y C 1G C D 2S W A 3I have a table in SQL Server 2000 which corresponds to the above worksheet.What's the best way to update columns F1, F2, F3 in the table using theAutoNo from both the table and worksheet?Thanks for any replies using ADO/VB/SQL and not DTS.
Dear AllI am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables' list but when i execute the code again, system throws an exception that table already exists.On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.Regards<code>Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\annieanna.xls;Extended Properties=Excel 8.0;")ExcelConnection.Open()'For existing Table.................METHOD 1Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection) 'For new Table......................METHOD 2Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)ExcelCommand.ExecuteNonQuery()ExcelConnection.Close()</code>
I am writing a custom application which will allow users to upload data in a Excel to SQLServer.
The problem I have is that Excel can come with a number of worksheets. I would ideally like to read from the default work sheet - is there a way I can specify that my code should only pick up the default worksheet?
Hi to all! I am working with SQL server 2000 and up until today, I used to import data in the Server using an Excel Spreadsheet, and the wizard. Last time today, that I tried to do that, I got the following message:
" The instruction at "0x02e21b80" referenced memory at "0x02e21b80". The memory could not be "read".
Any help, would be much appreciated! I am stuck here, and the most akward thing, is that up until today, I kept on importing data from Excel, with the same process with no problem...
Anyway, I thank everyone in advance, just for taking the time to read this thread! :D
We have a large user community that have created a number of MS EXCEL spreadsheets using MS OLAP Cubes as their input. Every month, once the cubes have been updated, the users can "refresh" their spreadsheets. These spreadsheets range from simple to complex, depending on the user's skill set and task to be performed.
Here is the problem... We are in the process of moving the cubes to a new server and the cube names are changing due to new Naming Standards. What can we do to minimize the impact to our user community?
I am facing a problem. I have a excel sheet with a 'single cell' displayed like this :
List of Employees FirstName Arranged StateWise California 97 Kathy 101 Jack 102 Suzanne Texas 103 Gonzalles 104 Ricardo 111 Kate Virginia 125 Jim 126 Rocky
and so on..where California is the state, 97 is empid and Kathy is empname
This has to be populate in a table
Employee Empid EmpFName Stateid
State StateId StateName
So during populating, we need to compare "California" against the statename field of State and pick up id and then populate the Employee table in sql server 2005
EXPECTED Output
97 Kathy 01 101 Jack 01 102 Suzanne 01 103 Gonazalles 02 104 Ricardo 02 105 Kate 02
and so on....
I have populated the excel cell into a staging table containing nvarchar(100)..but what to do after that?
I need to find a way to upload an Excel file into an MS SQL databaseusing a web control front end. I have my ASP.Net control (using C#)uploading a file to a directory, but the server people now tell me thatI cannot have a writeable area for the web and have a DTS see it as thisis too much of a security risk. So, I need a way to read the filedirectly into the database. I've no idea how to do this. Does anyonehave ideas? I know loading MS Office into the web server is out of thequestion. The webserver and database server are not the same physicalmachine.Thanks.
Greetings All,I have a excel file which is originally a sqlserver table that wasexported as a excel file. I have added more data to this excel fileand now want to import it again to its original table,i.e, it willoverwrite current data in the table but with no change in the schema.How should I handle the issue of PKs in the current table that will beover-written. I know sqlserver dose not adjust PKs when data isover-written, like my case.MTIA,Grawsha
