Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Finally crossed over ...

    Good morning (or afternoon, depending on your local).

    I've finally crossed over from Access to SQL Server. An interesting step fraught with surprises, syntax quirks and occasionally some exclamations of joy (just kidding).

    Anyway, here's my first question. I'm working on moving a currently functioning VB app from an Access backend to a SQL Server back end. In the process, I have been converting my app driven SQL calls to Server based stored procedures. Aside from a few issues (like SQL not liking concatenating string with the &), everything has worked out until this gem:
    Code:
        SET NOCOUNT ON
    	TRANSFORM Max(tE.EntryKey) AS Expr1 
    	SELECT tE.ProjectKey 
    	FROM TS_tblWeeks tW
    		INNER JOIN (TS_tblProjects tP
    		INNER JOIN (TS_qryGroup tG
    		INNER JOIN TS_tblEntry tE 
    	ON tG.GroupKey = tE.GroupKey) 
    		ON tP.ProjectKey = tE.ProjectKey) 
    		ON tW.WeekKey = tE.WeekKey 
    	WHERE (tE.WeekKey) = 1 And (tE.PersonKey) = 1
    	GROUP BY tW.WeekKey, tE.ProjectKey, tP.ProjectSort
    	ORDER BY tW.WeekKey, tP.ProjectSort, tG.GroupKey
    	PIVOT tG.GroupKey;
    SQL server reports:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'tE'.
    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'PIVOT'.
    The highlighted lines are the TRANSFORM and PIVOT lines.

    The query works fine in Access, and works fine through ADO.NET as a supplied query. My intention is to make the two integers in the WHERE clause to be parameters supplied to the procedure.

    It's something simple, isn't it?

    Thanks!
    Last edited by tcace; 05-13-08 at 09:35.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tcace
    I've finally crossed over from Access to SQL Server.
    About time! You won't look back

    No TRANSFORM in SQL Server. Any crosstabbbing should be done by the front end. Just return a normal set to the reporting tool. If you must do it in SQL then either:
    1) You will know how many columns your eventual output will contain, in which case you can use CASE statements (2k and 2k5) or the PIVOT operator (2k5)
    2) You don't know how many columns your eventual output will contain, in which case you need to use the above techniques but also using dynamic SQL. Read up on sp_executesql - you can paramtereise this.

    HTH
    Last edited by pootle flump; 05-13-08 at 09:43.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - ADO.NET! Linky - I have not tried this however the author is very good and usually turns out decent articles:
    http://weblogs.sqlteam.com/jeffs/jef...5/11/5101.aspx

    Also - may I ask if you are using parameterised SQL? The bit about the "&" makes one nervous....

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Thanks pootle.

    Unfortunately, I do not know how many columns I'll get until I run the query - it varies depending on the person and the week I'm looking up. Due to the anticipation of poor network performance, I'd like to have the server do all the work and only send the required data back. I'll lookup that sp you mentioned.

    tc

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tcace
    Thanks pootle.

    Unfortunately, I do not know how many columns I'll get until I run the query - it varies depending on the person and the week I'm looking up. Due to the anticipation of poor network performance, I'd like to have the server do all the work and only send the required data back. I'll lookup that sp you mentioned.

    tc
    I am only suggesting do the pivotting at the client. Defo do all the filtering and processing at the server. The volume of data is still the same.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - forget looking up the sp. Check out the below article - it covers just about everything you need to know about dynamic SQL including that sp.
    http://www.sommarskog.se/dynamic_sql.html

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    may I ask if you are using parameterised SQL? The bit about the "&" makes one nervous....
    I will be using parameters once I get it working.
    Code:
    (((tE.WeekKey) = @WeekKey) And ((tE.PersonKey) = @PersonKey
    I've only been in SQL Server for 3 days now and I already have about 10 sp's under my belt - one even makes a decision based on the first pull of data and then runs 1 of 3 different queries for the return.

    I've more history programming and in the world of code, the proper operator for concatenating strings is & instead of + (since + is a numeric operator).
    This works in Access as well, although I think both work in Access. I very quickly am learning what what works in Access but not in SQL Server. Such as using + not &, also using ' not "

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, ANSI SQL concatenation operator is "||" (minus the double quotes). You'll know now if ever you see that in other RRDBMSs

    + and & work differently with strings in Access.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, I'm about 10% through the referenced article and it's clear to me the water just got deep.

    I'm going to resist the urge to back away and do all the leg work in the app and keep reading ...

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In the SQL statements, what does the N' ...' syntax mean?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    N'...' explicitly converts the text in quotes from ASCI to Unicode. It isn't strictly necessary - the engine will convert implicitly.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Solution ... sort of

    Sad, sad. There is a way, but it's not pretty, and it's not recommended.

    For the interested, this link provides the stored procedure which will dynamically create a variable column pivot table. The risk is that it uses a global public table. Since SQL Srv 2005 does not allow using a parameter in dropping a table, you have two options (each with it's draw back):

    1) Stick with the global table ##pivot and either risk a collision (two people running the procedure at the same time) or insert some sort of check and wait routine
    2) Dynamically define the name of the global temp table, and rely on the sp cleanup to remove it

    In SQL Srv 2008, you can define a parameter of table type, which may yield the flexibility to DROP a table parameter, then all you need is a unique name, CAST(CAST(GETDATE() AS REAL) AS NVARCHAR()) + @SuppliedKey, for example, and you can avoid both issues noted above.

    Most of what I found in google indicates the "proper" way to do it is to format the data in the front end.

    **sigh** oh well.

    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah - that was written in 2001. There are better ways * in SS 2005.


    * NOTE - better is relative - front end is still best. Any good reason you are reluctant to get the front end to do this? Remember that Access is a front end tool - as such it is not weird that you can't port everything to SQL Server.

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Any good reason you are reluctant to get the front end to do this?
    ummmm, zeal, laziness, the excitement of doing something new ...

    Since the app was already written using the TRANSFORM statement sent to Access and simply dumped the already pivoted and formatted data into a recordset, doing it in the front end means a little bit of leg work.

    Also, I figured it would be more efficient to do it on the server side. Since the server will have to compile the dynamically written SQL, the efficiency delta is probably negligible.

    thanks for you help.

    Now I have to figure out the SS equivalent of the FIRST operator ...

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Server side RDBMSs are there to manage security, maintain a transactionally & relationally correct state and retrieve data as efficiently as possible. Even ordering output is often frowned upon. There is one server backend and N front ends so you shunt as much stuff that does not fall into the above three domains to the front end. Basically distribute as much processing as you can.

    There is none. FIRST and LAST are relationally naughty. There is no first and last record in a set - a set is unordered. You need some sort of column value (like a date of insert, perhaps an identity) that you use in a corrolated sub query to simulate first and last.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •