May 13, 2014 at 9:28 am
In my previous posts I have been migrating my queries from Access (2013) to and SQL server (2005). For the most part it works fine, but my recent migration, I get the following error on parsing :
Msg 102, Level 15, State 1, Procedure GetThisLocCode, Line 18
Incorrect syntax near '.'.
here is the Select statement:
SELECT DISTINCTROW dbo_StaffListings.LocCode, dbo_StaffListings.AutoNumber, dbo_StaffListings.OfficeNumber,
dbo_StaffListings.Column, dbo_StaffListings.Floor, Left([dbo_StaffListings]![LastName],1) AS Heads,
dbo_StaffListings.LastName, dbo_StaffListings.ShowName, dbo_StaffListings.FirstName, dbo_StaffListings.Extension,
Len([dbo_StaffListings]![Extension]) AS ExtLen, dbo_StaffListings.Department, dbo_StaffListings.FirstAid,
dbo_StaffListings.FireWarden, dbo_StaffListings.SpecialStatus, 1 AS Dummy, dbo_OfficeCodes.Address1,
dbo_OfficeCodes.Address2, dbo_OfficeCodes.City, dbo_OfficeCodes.StateProv, dbo_OfficeCodes.PostalZip,
dbo_OfficeCodes.PhoneNo, dbo_OfficeCodes.FaxNo
I do notice that my column dbo_staffListings.Floor has Floor in pink. Is this the issue?
May 13, 2014 at 9:40 am
Try putting floor in square brackets [ ] ?
May 13, 2014 at 11:18 am
tried that, but no. Still exact same error...
May 13, 2014 at 11:32 am
assuming you already have
dbo_StaffListings and dbo_OfficeCodes table created and data populated in SQL server
then
start from scratch and rebuild in SQL...I don't think there is shorterm quick fix for all solution.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2014 at 11:58 am
I was thinking that...
I did comment it all out and try just the bare bones, and that worked.
I am not the best at the syntax for SQL, so it's a slow road... The statement was generated from the query builder in Access, maybe I will attempt to do that with a view first, then use the stored procedure to filter with the passed parm from access.
I did have issues with a previous migration of SQL from Access, had to do with " and ' mostly... After I get it working, I will post the comparing SQL statements if anyone is interested.
EDIT:
Is there a way to change the view when editing a VIEW(query) so that you can see the builder? I find it much easier seeing the Criteria Pane.
May 13, 2014 at 12:22 pm
jdasilva (5/13/2014)
I was thinking that...I did comment it all out and try just the bare bones, and that worked.
I am not the best at the syntax for SQL, so it's a slow road... The statement was generated from the query builder in Access, maybe I will attempt to do that with a view first, then use the stored procedure to filter with the passed parm from access.
I did have issues with a previous migration of SQL from Access, had to do with " and ' mostly... After I get it working, I will post the comparing SQL statements if anyone is interested.
suggest you build as view first......for starters don't think DISTINCTROW will work...use DISTCINT...
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2014 at 12:41 pm
Will try that. Found the Criteria window (right click the View and select DESIGN)
man, this interface is not very user friendly... Well I guess when it comes to nice interface vs. overall power, I'll go with the power house... 🙂
May 13, 2014 at 12:42 pm
No clue if this will work but the code will at least parse correctly.
SELECT DISTINCT dbo_StaffListings.LocCode, dbo_StaffListings.AutoNumber, dbo_StaffListings.OfficeNumber,
dbo_StaffListings.[Column], dbo_StaffListings.Floor, Left([dbo_StaffListings].[LastName],1) AS Heads,
dbo_StaffListings.LastName, dbo_StaffListings.ShowName, dbo_StaffListings.FirstName, dbo_StaffListings.Extension,
Len([dbo_StaffListings].[Extension]) AS ExtLen, dbo_StaffListings.Department, dbo_StaffListings.FirstAid,
dbo_StaffListings.FireWarden, dbo_StaffListings.SpecialStatus, 1 AS Dummy, dbo_OfficeCodes.Address1,
dbo_OfficeCodes.Address2, dbo_OfficeCodes.City, dbo_OfficeCodes.StateProv, dbo_OfficeCodes.PostalZip,
dbo_OfficeCodes.PhoneNo, dbo_OfficeCodes.FaxNo
from SomeTable
The biggest issue you have is that you are using a lot of reserved words as column names. This is generally not a good idea because it makes things a lot more painful to work with. Consider that the names are intentionally vague they don't make good column names anyway. Consider columns names like "Column". It has no meaning whatsoever. Renaming that something meaningful will make your system a lot easier to work with.
I would recommend a couple of things here. First of all you should use a table alias instead of the entire table name over and over. Secondly is I would rename these tables and get rid of the schema + underscore. The dbo_ is just noise that makes this harder to work with than needed. It gets especially difficult if you reference these by schema.
dbo.dbo_StaffListings
Ugh!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 12:44 pm
jdasilva (5/13/2014)
Will try that. Found the Criteria window (right click the View and select DESIGN)man, this interface is not very user friendly... Well I guess when it comes to nice interface vs. overall power, I'll go with the power house... 🙂
I would recommend not using the visual designer for much of anything. You will create far cleaner code if you write it yourself instead of using a code generating tool. It is a bit slower at first but in the long run it will reap huge benefits not only in your ability to produce code faster, but also the performance of the code you write.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2014 at 7:34 am
thanks Sean for the tips.
unfortunately changing the names currently used would be quite a lot of work as it is not only tied to Access but several Visio floor plan files across the country. Though the names may not mean much to you, they are actually quite specific. Floor is the floor number in that office. Column is the column which the cube/room is closest to. These are required for our health and safety/Fire regulations. I guess my problem was I used to specific a name for these instead of an abreviation, such as fl for floor...
my other problem is that I have 0 training in SQL. Everything I have done to date is self taught. Would like to take a class on SQL and Oracle, but time is not on my side (nor is company budget at this time). I like the criteria window as it shows me what the code looks like for certain things. Also, since the syntax is slightly different that what is access 2013, I can build the structure, then see the code then work off the code. I'll try not to depend on it, as you are right straight coding is always better than a GUI of any type.
Thanks again all.
May 14, 2014 at 8:02 am
jdasilva (5/14/2014)
thanks Sean for the tips.unfortunately changing the names currently used would be quite a lot of work as it is not only tied to Access but several Visio floor plan files across the country. Though the names may not mean much to you, they are actually quite specific. Floor is the floor number in that office. Column is the column which the cube/room is closest to. These are required for our health and safety/Fire regulations. I guess my problem was I used to specific a name for these instead of an abreviation, such as fl for floor...
my other problem is that I have 0 training in SQL. Everything I have done to date is self taught. Would like to take a class on SQL and Oracle, but time is not on my side (nor is company budget at this time). I like the criteria window as it shows me what the code looks like for certain things. Also, since the syntax is slightly different that what is access 2013, I can build the structure, then see the code then work off the code. I'll try not to depend on it, as you are right straight coding is always better than a GUI of any type.
Thanks again all.
I get what you mean about requiring lots of changes to other applications. You may be better off not changing anything for the current project.
In the future I would actually recommend not using abbreviations but make the column names more meaningful. Instead of Floor use FloorNumber. That way when you are no longer there and somebody else is looking at the database they know instantly what that means. If you started working on a new database and all the column names were really short abbreviations it would be exceptionally painful to work with.
When you use meaningful names your code can actually tell the story about what the query is doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2014 at 10:08 am
Thanks. I will. In programing (do a bit of VBA) I always fight with var names. Too long vs. not discriptive enough.
Also, I see you point on the "dbo_", but that was carried over from Access, which is part of the issues I ran into. Thanks again for putting up with the newbie questions... I've been in that seat before.
May 14, 2014 at 10:22 am
jdasilva (5/14/2014)
Thanks. I will. In programing (do a bit of VBA) I always fight with var names. Too long vs. not discriptive enough.Also, I see you point on the "dbo_", but that was carried over from Access, which is part of the issues I ran into. Thanks again for putting up with the newbie questions... I've been in that seat before.
The upside is that with sql server 2008+ it has built in intellisense. If you are actually on 2005 you can get some pretty excellent third party tools to provide intellisense. Then there is no worry about the names being too long because you don't have to type the whole thing anyway. It really allows the flexibility to use meaningful names.
No problem with the questions. We were all newbies at one point or other with nearly every topic...in fact I am still a newbie with a lot of topics. Helping others that appreciate it is enjoyable and helps me continue learning too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply