open row set where clause

[gtjr921]

I have 2 problems that are related
1st problem:
I am trying to pull data from an excel spreadsheet
IF i just execute the query with no where statement it runs fine if i use where fieldname is null it works, but if i
try to i use a specific date on the check date field i just get
Data type mismatch in criteria expression

 Select ssn,[ckdate],HCOPAY FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 'SELECT SSN,[Check Date] as ckdate,[H125 - deduct] as HCOPAY FROM [Sheet1$] where [check date]  is not null order by [check date] desc')

if i change the check date to be specific that is when i get the error Msg 7320

where [check date] = "08-09-2006"

2nd Problem: how can I join the xls file to a DB table
I tried

 Select id,[ckdate],hco,HID1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 'SELECT s1.id,s1.[check Date] as ckdate,s1.Hco,MyDB.HID as HID1 FROM [Sheet1$] as s1, MyDatabase.dbo.MyTableName as MyDB where mydb.HID1 = s1.id  [check date]  is not null order by [check date] desc')

When i do that i get this error
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

I am logged in as as DBA

 

[rac2]

I have a lot of experience with excel and MS SQL Server, but none with linking them as you have done. Nonetheless, I have some ideas that may be worth pursuing.

In Excel, "08-09-2006", is a string, pretty much forced to be a string. Maybe the OPENROWSET() function in SQL Server does not convert the string to an Excel date value. Maybe Jet OLEDB does not do that either. Maybe we need to do that explicitly with an Excel function DATEVALUE("2007-03-08").

Then the other problem. Maybe the JOIN should be done outside the Excel query. After all it is the result of that query that is being joined to the SQL Server table. Maybe the OPENROWSET() can be treated like a subquery.

So, maybe

Select s1.id, s1.[ckdate], s1.hco, mydb.HID1 

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 
                'SELECT id, [check Date] as ckdate, Hco
                 FROM [Sheet1$] 
                 where [check date] = DATEVALUE("2007-03-08")'
     ) s1

JOIN MyDatabase.dbo.MyTableName MyDB ON mydb.HID1 = s1.id

ORDER BY s1.[check date] desc

I would try this on my system but I dont seem to be able to make the OPENROWSET() connect to a spreadsheet in QueryAnalyzer.

So, these are just some ideas.

 

[harebrain]

Are you using SQL Server or Access?

 

[gtjr921]

sql server

 

[gtjr921]

sql server 2005

 

[gtjr921]

The date value worked, but when i tried to do the join i get

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" supplied invalid metadata for column "ssn". The data type is not supported

my join query looks like this now

Select ID,[ckdate],hcopay FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 'SELECT s1.id,s1.[check Date] as ckdate,s1.[H125 - deduct] as HcoPay,mydb.ID FROM [Sheet1$] 
where [check date]= DATEVALUE("2006-08-09")')   S1 
JOIN Health.dbo.EmployeeInfo MyDB ON mydb.ID = s1.ID  order by s1.[ckdate] desc

 

[gtjr921]

oops "supplied invalid metadata for column "ssn"."
ssn and id are same columns

 

[gtjr921]

I got the join to work to a point, The ID field in the Spreadsheet must be an int or something, it is a carchar in the DB. I can't get them to match up if i use an inner join
Then i get no rows, but if i use a left join I get the row of course they are not joined properly.
I tried to use convert and CSTR for the id field but it either says the built in function does not exist if i use cstr with the openrow set.
If i use convert or to_char in the query I get a permission error.
I tried modifiying the registry to change the ad hoc permissions but the key was not there. I had already changed them in surface area config
Any ideas?
Here is my join query

Select s1.id,s1.[ckdate],s1.hcopay, MyDB.id as id2,MyDB.EmpID FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES',
'SELECT (id),[check Date] as ckdate,HcoPay FROM [Sheet1$] as s1  order by [check date] desc'
) as s1    Join Mydatabase.dbo.mytable as MyDB On s1.id= mydb.id

 

[AlexCuse]

try this:

Join Mydatabase.dbo.mytable as MyDB On [b]cast(s1.id as varchar)[/b]= mydb.id

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gtjr921]

That worked, the last thing i am trying to do
is to only get the dates from the spreadsheet that are not already in the Sql DB

I tried

Select s1.id,s1.[ckdate],s1.hcopay, MyDB.id as id2,MyDB.EmpID FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES',
'SELECT (id),[check Date] as ckdate, HcoPay FROM [Sheet1$] as s1  order by [check date] desc'
) as s1  Inner Join Database.dbo.Tablename as MyDB On mydb.ID  = Cast (s1.ID as VarChar)
Where ckdate is not null 
and  ckdate  not in ('select distinct cast (check_date) from healthpaycor.dbo.healthpaycor')

I also tried to cast both dates as varchar in the where clause If i use cast I get "no
Conversion failed when converting datetime from character string" If i don't use cast i get datest that are already in the Sql DB.

 

[gtjr921]

that should be the opposite if I do not use Cast then i get the above mentioned conversion failed error

 

[AlexCuse]

What format are your excel dates in?

You want to use CONVERT funtion for datetime, because this will let you apply a format to the varchar result. You will only want to convert on the SQL side, because excel data could be a mess.

Something like this would work maybe?

and  ckdate  not in ('select convert(varchar(10), check_date, [b]101[/b]) from healthpaycor.dbo.healthpaycor')

The 101 format will tell CONVERT to return a string looking like this:

04/04/2007

For more formatting options check out this link:

http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

About 1/3 of the way down the page is some more formatting options (although the whole article is probably worth reading)

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gtjr921]

I hate being a pest I tried that I used that exact clause and the best i can get it for is to return the all rows that are even if i put "not in" it still gives me the dates that are in the DB and the one date that is not in the db table. If i do "in" instead of "not in" I do not get any rows back!
I tried all the other formats from the link you mentioned to no avail. I have worked with dates in sql to an extent but never like this interacting with excel.
Your help has been greatly appreciated if you have any clue as to what else I can try I would be grateful.

 

[AlexCuse]

Can you post what the date format is in the excel file?

Ignorance of certain subjects is a great part of wisdom

 

[gtjr921]

originally excel was like 4/4/2007
and i also changed it to be like 04/04/2007
Neither way worked

 

[AlexCuse]

Hm, what do the excel values look like when selected in SQL? Maybe they are changing somehow when selected? I try to avoid using OPENROWSET, so I've never run into this problem before.

Ignorance of certain subjects is a great part of wisdom

 

[gtjr921]

Excel date in sql
2006-08-12 00:00:00.000

date from sql db table I am trying to compare
2006-08-11 00:00:00.000

So it makes no sense why the query is acting like it is

 

[AlexCuse]

Ok...

Looks like both are handled as datetime so you won't need to convert either to varchar.

So maybe this?

and  ckdate  not in ('select distinct check_date from healthpaycor.dbo.healthpaycor')

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gtjr921]

unfortunately that was the first thing i tried which throws this error
"Conversion failed when converting datetime from character string"

Which also makes no sense since both fields are formatted as dates.
I very much appreciate your help on this.

 

[AlexCuse]

are you sure check_date in healthpaycor is a date/time data type and not a varchar column that just looks like a date/time? You need to figure out which is getting treated as a string, and I imagine it is the SQL table. The fact that excel data looks like 4/4/2007 in the spreadsheet and 2007-04-04 00:00:00.000 in SQL means that is probably being treated as a date.

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom