August 5, 2008 at 1:45 pm
Wht is the best way(in t-sql) to import 2 columns from Excel sheet into a table, this will be a job where i need to look for excel file on my drive.
Its not like an import but it is an update which i want to do it thrugh t-sql
August 5, 2008 at 2:11 pm
How about OPENROWSET ? (look it up in BOL)
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 2:19 pm
yeah i am trying to do that.
how cud i configure this...
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
August 5, 2008 at 2:23 pm
can i use this in sql serevr 2005
sp_configure 'Ad Hoc Distributed Queries', 1
August 5, 2008 at 2:25 pm
Sounds like it may not be configured by default, or it's been intentionally turned off. Check first with your admin/DBA, or if you're it, then you probably need to log on to the server with an id that's in the local administrators group and run the "sac" tool ("sac" stands for Surface Area Configuration). Check in BOL for the how to. If you're not the DBA/admin, you'll probably need them to reconfigure, assuming they're willing.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 2:28 pm
After changing the settings and tried selecting excel sheet in sql i got this error
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
August 5, 2008 at 2:35 pm
Having never done this myself, and assuming that it must be possible, I would check the BOL to ensure I was meeting it's exact requirements, and see if there was any sample code to copy from. Beyond that, I'd probably be stuck.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 3:19 pm
Update Site
SET
Site.CorpOHPer,sp.CorpOHPer=isnull(sp.CorpOHPer,''),
Site.DivOHPer,sp.DivOHPer=isnull(sp.DivOHPer,'')
Site.RegOHPer=sp.RegOHPer,
Site.BurdenPer=sp.BurdenPer
from StagingSiteProfile sp
WHERE Site.SiteID=sp.SiteID
I am using ISNULL so that if there is a null value for that column I want to replace it by blank, buti get 0 here instead blank.
is there a way i can ignore update if there is a null??
August 6, 2008 at 7:09 am
It could be a data type problem. Whenever you insert data from Excel, SQL Server looks at some number of rows at the beginning of the data to see what data type each column is. If you have inconsistent data within those rows, you're going to have trouble. I still remember a DTS import from SQL Server 2000 where I had to move the longest row to the top of the spreadsheet to ensure that SQL Server saw a particular column as varchar longer than 255.
Check for those possibilities first and then, you might even have to check for 0 as well, unless there could actually be a valid 0 value, in which case you might have some trouble. You might then have to change any actual 0's to something else that's completely unique in that column, and then change them back after import.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply