Converting code from Access to SQL Server

[rwn]

I have this SQL statement that worked great in an Access database. Now the database is running from a SQL server and needs to be converted to SQL. When trying to run the Select as it is below, the message is: Incorrect Syntax" and the file path. Can anyone assist in rewriting the code for a SQL server? Thank you very much.




SELECT `Work Schedule - Promised Welding Hrs per Work Week`.W, `Work Schedule - Promised Welding Hrs per Work Week`.SumOfSumOfDifference
FROM `U:\Lance\Weekly Reports\jbudrpt`.`Work Schedule - Promised Welding Hrs per Work Week` `Work Schedule - Promised Welding Hrs per Work Week`
ORDER BY `Work Schedule - Promised Welding Hrs per Work Week`.W

 

[bborissov]

O GOD!!
What are these names

SELECT WrkSchProm.W,
       WrkSchProm.SumOfSumOfDifference
FROM [Work Schedule - Promised Welding Hrs per Work Week] WrkSchProm
ORDER BY WrkSchProm.W

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[rwn]

Example =`Work Schedule - Promised Welding Hrs per Work Week`.W, `

Table = `Work Schedule - Promised Welding Hrs per Work Week`.
Field = W.
also:
Field = SumOfSumOfDifference.


I'm working with someone elses table design and trying to convert to SQL.

 

[SgtJarrow]

I don't think bborissov was not asking "what" your table names were. He was commenting on how "unconventional" they are. The names are a long and not formatted in any "normal" fashion.

But to elaborate a bit on his post, in SQL you use square brackets to enclose names with spaces and special characters.

So a table name like "happy fun for everyone":
In Access you would use 'happy fun for everyone'
In SQL you would use [happy fun for everyone]

More than likely if you replace all the single quotes with the appropriate square bracket, you will be much closer to fixing your issues.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[rwn]

I realize how the table names it might look, but they were allcreated by another person and I inherited the database that they reside in.


replace the single quotes with appropriate square brackets, but how do I address the FROM `U:\Lance\Weekly Reports\jbudrpt`.`

 

[gmmastros]

I'm guessing that jbudrpt is some sort of file that contains data you want to get. What type of file is this? Excel, Access, ASCII, etc...

The most appropriate advice depends on the type of file your data is stored in.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rwn]

The jbudurpt.mdb is an access file. I should have stated that in the beginning so there are no questions or assumptions.

 

[gmmastros]

Yes, you should have stated that at the beginning. You would have gotten appropriate advice sooner. Regardless, there are several ways to accommodate this.

I would suggest you create a linked server from SQL to your access database. Linked servers are a little more difficult to set up, but once you have it set up, it's easier to use. I would only suggest a linked server if this query will repeatedly be run. For "one-time" queries, I would suggest OpenQuery or OpenRowset instead.

I suggest you take a look here:

http://sqlserverpedia.com/wiki/Linked_Servers/Remote_Stored_Procedures_Overview

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rwn]

I'm trying this and keep getting can't open row set.

SELECT [Work Schedule - Promised Welding Hrs per Work Week].W, [Work Schedule - Promised Welding Hrs per Work Week].SumOfSumOfDifference
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\JobBOSS\JBUDRPT.mdb'; 'lonestar'; 'lonestar' [Work Schedule - Promised Welding Hrs per Work Week] )
ORDER BY [Work Schedule - Promised Welding Hrs per Work Week].W

 

[SQLBill]

I think you are missing a comma between the second 'lonestar' and the [Work Schedule - Promised Welding Hrs per Work Week]

SELECT [Work Schedule - Promised Welding Hrs per Work Week].W, [Work Schedule - Promised Welding Hrs per Work Week].SumOfSumOfDifference
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\JobBOSS\JBUDRPT.mdb'; 'lonestar'; 'lonestar', [Work Schedule - Promised Welding Hrs per Work Week] )
ORDER BY [Work Schedule - Promised Welding Hrs per Work Week].W 
[\code]

SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875