I am trying to load data from an Excel spreadsheet file into SQL Server 2005 Express. I understand that DTS is the best tool for doing this but from my research it appears that DTS is not available with the Express edition and the import wizard that does come with Express is not well suited for this type of conversion.
Does anyone have any suggestions for how to achieve this objective? Thanks for any help you can provide.
I would like to import an Excel file into my SQL Server 2005 Express db. I'm using SQL Server Management Studio Express to manage my db.
I appreciate your suggestions,
I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table?
View 2 Replies View RelatedIs there a way of extracting data from MS Excel using MS SQL Server 2005 Express?
View 1 Replies View RelatedHow to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
Hi
as mentioned in title i'm trying to move data from excel into a table in a sql server 2000 database. We have spreadsheets that produce data in excel using querytables.
we want a system the other way, by typing data into a spreadsheet, pressing a button which pushes the data into a sql server table.
I know this is half VBA and half SPORC question.
but i hope you can help!!
I have been programming an application with VC++ 2005 and SQL Server 2005. I have converted an old 16-bit database to 32-bit managed code and SQL server and the application seems to be good. Now I want to deploy the application to another server for testing.
I have installed XP SP2, Windows Installer 3.1, Net framework 2.0 and SQL Server 2005 express to the test server. I have transferred the application with WI 3.1 and the program works well in the test server till the first SQL command. I have made a back up of the database and restored it in the test server. In the test server I can log in the database with Server Management studio and I can read the data there correctly. I have enabled both named pipes and TCP/IP for the database in the test server. With Surface Area Configuration I have enabled Local and Remote Connections Using both TCP/IP and named pipes. I only need Windows authentication at this time.
After all this when I come to the first SQL command in the application on the test server I receive the error message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 Could not open a connection to SQL Server).
My connection string to the database is:
'connection->ConnectionString = "Persist Security Info=False; Integrated Security =SSPI;"
"Data Source=TESTSERVER; Initial Catalog=TESTDATABASE;";'
When I use "Data Source=DevelopmentServer", the application works well on the development server.
Can't understand what is still wrong. Can you possibly have an answer for me?
Hi,
View 3 Replies View RelatedI have been working with SQL Server 2005 EXPRESS for a while and now need to import some basic demographic data into my database from an Excel file.
I only have Management Studio EXPRESS to work with. How can I import Excel data into my database? Is there a mgic third party tool for this or can I do it with Management Studio EXPRESS?
Hi
I am wishing to
* take a table of data into a C# CLR procedure and store it in an array
* take a second table of data into this procedure row by row, and return a row (into a third table) for each row (and this returned row is based on the data in the array and the data in this row).
I am new to CLR programming, and pulling my hair out at the moment. Im sure that what Im trying to do is simple, but I am not making any progress with the on-line help and error messages L. I have now described what I am trying to do in more detail, firstly in English, and then in a T-SQL implementation that works (for this simple example). Im looking for the C# CLR code to be returned containing preferably two parts:
* the C# code and
* the CLR code to make it live
Since I am not sure where my coding is going wrong. (I think it should be possible to read in the one table, and then loop through thte second table, calculating and returning the necessary output as you do the calculations...
Problem in English
Consider a situation where there are three tables: DATATABLE, PARAMETERTABLE and RESULTSTABLE.
For each row in PARAMETERTABLE, I will calculate a row for RESULTSTABLE based on calculations involving all the entries form DATATABLE.
I am wishing to do this in a C# CLR for performance reasons, and because the functions I will be using will be significantly more complex, and recursively built up.
Consider the following table structures
DATATABLE [i int, datavalue real]
[1,12.5]
[2.10]
[3.14]
[4,17.5]
(where numberofrows is 4)
PARAMETERTABLE [parametera real, paramaterb real]
[10,2]
[11.7,1.1]
RESULTSTABLE [resultvalue real]
[20.5]
[18.26]
The values of the results table are calculated as the
sum (for i = 1 to numberofrows)
of
(Parameter1+i*parameterb-datavalue)^2
Which leads to the values shown.
T-SQL Implementation
-- Set up database and tables to use in example
USE master
GO
CREATE DATABASE QuestionDatabase
GO
sp_configure 'clr enabled', 1
GO
USE QuestionDatabase
GO
RECONFIGURE
GO
CREATE TABLE DataTable (i INT NOT NULL, DataValue REAL NOT NULL)
GO
CREATE TABLE ParameterTable (ParameterNumber INT NOT NULL, ParameterA REAL NOT NULL, ParameterB REAL NOT NULL)
GO
CREATE TABLE ResultsTable (ParameterNumber INT NOT NULL, ResultsValue REAL NOT NULL)
GO
--Initialise the Tables
INSERT INTO DataTable (i, DataValue) VALUES (1,12.5)
INSERT INTO DataTable (i, DataValue) VALUES (2,10)
INSERT INTO DataTable (i, DataValue) VALUES (3,14)
INSERT INTO DataTable (i, DataValue) VALUES (4,17.5)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (1, 10, 2)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (2, 11.7, 1.1)
-- The TSQL to be rewritten in C#, to produce the Output as hoped
INSERT INTO ResultsTable (ParameterNumber, ResultsValue)
SELECT ParameterNumber, SUM((parametera+i*parameterb-datavalue)*(parametera+i*parameterb-datavalue)) AS b
FROM DataTable
CROSS JOIN
ParameterTable
GROUP BY ParameterNumber
-- Output as hoped
SELECT * FROM DataTable
SELECT * FROM ParameterTable
SELECT * FROM ResultsTable
-- which produced
1 12.5
2 10
3 14
4 17.5
1 10 2
2 11.7 1.1
1 20.5
2 18.26
-- but I hope to do the same with something like:
CREATE ASSEMBLY *** FROM 'C:***.dll'
CREATE PROCEDURE GenerateResultsInCLR(@i int, @r1 real, @r2 real)
RETURNS TABLE (i int, r real)
EXTERNAL NAME ***.***.***
EXEC GenerateResultsInCLR
This is a simple example, that can be easily written in T-SQL. I am looking to develop things that are recursive in nature, which makes them unsuited to T-SQL, unless one is using cursors, but this becomes very slow when the parameter table has 1m records, and the data table 100k records. This is why the datatable must be read in once, and manipulated many times, and the manipulation will need to be in the form of a loop.
Thanks very much for your help.
Go well
Greg
Hi All,
We have a Progress DB in our Company. We are trying to move all the data to SQL Server 2005. When I try to run the Import Data task from SQL Server 2005 the radio button called "Copy data from one or more tables or views" is getting disabled and it is asking me to write a SQL script to copy all data from 120 tables.
I am using a .NET Framework Data Provider for ODBC when I run the Import Data task from SQL Server 2005.
I am using the DataDirect 4.1 32-Bit Progress SQL92 v9.1D ODBC Driver to connect to Progress DB. I am getting a connection but the copy feature is getting disabled.
Can anyone please help me to resolve this issue, or even if you provide me with some pointers that will be really helpful.
Thanks
Suresh
Hi,
I'm trying to learn some VB programming with the VB 2005 Express Absolute Beginner Series video tutorials (which I think is great) and have come across a problem that I can't solve.
When I follow the instructions in Lesson 9 (Databinding Data to User Interface Controls) my application will display the data from the database correctly and I can edit it (and as long as the debugger is running the data remains changed). However, the changes won't propagate back to the database. I don't get any error messages but after I edit the data, save (with the save button on the BindingNavigator toolbar), and end debugging the data in my database remains unchanged. When I use a MessageBox to show how many rows where edited/updated in the
Me.myTableTableAdapter.Update(Me.myDatabaseDataSet.myTable)
I get the correct number back. I'm sure the problem is not due to coding errors since I've also tried running the accompanying Lesson 9 project file that can be downloaded from MSDN and the problem persists.
I'm using Windows XP SP2, SQL Server 2005 Express Edition and VB 2005 Express Edition. I've tried installing SQL Server 2005 Express with a number of different settings, including default settings, but it doesn't make any difference.
Would greatly appreciate any feedback on this as I'm keen to resolve this problem so I can get on with the next tutorial lesson.
Thanks,
Ieyasu
Hi there. i have the following excel spreadsheet that needs to be moved into a database:
ADDRESS
STREET
SURNAME
GIVEN
PHONE
CITY
POSTAL
NOTES
TERR
40
Goodless Crt
You
M
(416) 123-1234
SC
M1B 1A1
SC-123-12
59
Bellevue Ave
Doe
Jon
(416) 123-1234
SC
M1B 1A3
SC-123-13
Most of these fields are going to be created as reference tables... and the main contact list table will only include a fk to the actual value.
eg) the contact table for the above will look like:
ADDRESS
STREET
SURNAME
GIVEN
PHONE
CITY
POSTAL
NOTES
TERR
40
1
You
M
(416) 123-1234
1
M1B 1A1
5
59
2
Doe
Jon
(416) 123-1234
1
M1B 1A3
6
My question is, what is the best way to move all this data into SQL Server Express?
I've been playing around with the LinkedServers section in sql and have managed to create a connection to my excel workbook. I am able to select all worksheets as tables... and i've also been able to select all records from the main contacts sheet.
not knowing any better, my initial thoughts are to create separate sql statements for each reference table.
eg) select street from excellink...contact$
and then combine it somehow with an insert statement into the streets table in my database.
Is this the right approach? if it is, can you help with the syntax of the sql statement?
Please and thanks.
I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.
Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686
My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.
Anyone with more clues than me?
thanks
Problem: Moving data from mysql to sql server 2005
I am trying to pull data over from mysql to sql server. First the import wizard greys out so I have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!
Anyway i tried going through the ssis route cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that ?
I hear someone mentioned of using excute sql task which can use mysql odbc 3.51 driver. but since i am new how do i get it to work. say for example in the excute sql task i run a statement like select * from addr. then what?
cuz eventually i want the result to be saved in a sql server table called addr. How can i get the result from that excute sql task and put it inside of an addr table in sql server. should i save the result to a variable of type object. but then how do i get the data from object and tell sql server in the designer that the result contains these columns and it needs to map to these columns in the addr table of sql server.
Very confused. i wish the first option would have given me results which an enterprise ETL gives. but apparently it is too slow that it wont be acceptable in a production envrioment. when i will have millions of rows coming in .
Please anyone can help me in this.
Thanks for the help!
Hi all,
Posted - 12/05/2007 : 01:54:18
How to export data from SQL Serevr 2005 Express to Excel 2007 using OPENROWSET command.
I have tried the following code But getting error
sSQL.Format("insert into OPENROWSET('Microsoft.JET.OLEDB.4.0','Excel 8.0;Database=%s;','Select * from [%s$]') select * from [%s]", sExcelPath, sSheetName, sTable);
But getting the following error
The OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" reported an error
I am using automation object for creating EXcel .I have added the header also.
Any help is appreciated
Thanks
Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way? Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
Hi, please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .
View 8 Replies View RelatedHi
I have managed to add the DTS wizard as a tool and saved the routine. However, I do not know how to schedule my PC to automatically run this DTS procedure. I have tried to use scheduled tasks, but it does not seem to want to start.
Pls help.
hi, does anyone know how I can make a connection to a 2000 db thru ssis?
View 8 Replies View RelatedHi,
I have an excel sheet in which there is some data in sheet1,sheet2.I need to transfer this 2 sheets data to single table using a single package.How can i do this in SSIS.
thanks in advance
Pradeep
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?
View 3 Replies View RelatedHi,
I encounter a bug while exporting to excel a matrix.
one cell is shifted to right and so i have wrong numbers and empty cell in the middle of the matrix.
instead of :
ABS
Recent College Graduate (C)
Male (M)
Total
Female (F)
Male (M)
Total
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
232
34
54
56
56
5
24
56
56
56
34
23
43
54
56
I get:
ABS
Recent College Graduate (C)
Male (M)
Total
Female (F)
Male (M)
Total
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
232
34
54
56
56
5
24
56
56
56
34
23
43
54
56
does anyone familiar with a solution to this issue?
Thanks. Yuval.
Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.
Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.
Any ideas on how to break the chain here are welcome.
hi all,
how to import data from excel to sql server 2005 using asp.net 2.0 and c#
Ashok
I have been trying to use the dtswizard.exe in SQL Server 2005 to import Excel 07 spreadsheets with no success.
Any idea how this can be done?
As a work around I have been importing the Excel 07 data into Access 07 and saving the database as the older mdb extension, THEN importing the mdb file into SQL Server 2005. Seems silly though.
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Insert into Pamphlet
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]')
But it is giving the following error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.
Please help me.
Hi all,
I try to import data from excel file, my excel file have column called Name, the value of this column is text: ex: Binh Chanh, 1,2,3,4,5.....When i import into sqlserver 2005, these rows which have value 1,2,3,4,5 (number) , now have Null value in SQL server 2005.
How can i fix this error?
Thanks,
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?
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
Hello,
I was wondering if it was possible to move a database from the Express edition of SQL Server 05 to say an enterprise edition without having to make major updates or changes to the database.
Thanks in advanced.
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
http://msdn.microsoft.com/vstudio/express/sql/download/
Thank you,
Bashman