SQL Server Resources


20 Responses to “SQL Server Resources”

  1. Allen McEuin Reply July 13, 2011 at 9:04 am

    Victoria and community. I am going to install the SQL 2005 SP4 service pack this weekend. I have GP 10 SP4. Any known issues out there? Also, I don’t remember off hand, but does GP 10 require me to do any further steps after installing the SQL server service pack?

  2. Adam Reply May 12, 2011 at 2:10 pm

    I’ve learned much from your website and it is one of my regular references when working with GP data.
    My question is, how do I give GP access to data outside of the GP database?

    I have another database on the GP server for auditing purposes, which I will call AUDIT. I created a view called SOPAudit with data from the AUDIT database. I put that view in the GP database. I granted select to DYNGP on the view and granted SQL Table Security to the view using the tools with Smartlist builder. I built a smartlist off of the view. The smartlist works fine only when I am logged in as SA. If I am logged in as a users, even a poweruser, the results of the smartlist is nothing. Obivously I need to grant permissions to the GP users to the AUDIT database. Is there a way to do this from inside of GP? If not, how do I go about granting permissions to all GP users from withing SQL?

    Thanks

    Adam

    • Victoria Yudin Reply May 12, 2011 at 2:36 pm

      Adam,

      Does DYNGRP actually have permissions to the other database? If not, that might be the issue.

      -Victoria

      • Adam Reply May 12, 2011 at 4:27 pm

        No DYNGRP does not have permission to the other database. That’s not what I am clear on. Is it possible to give a role in databaseA permission to use database B? Or does one have to create that role in database B and maintain it separately?

        • Victoria Yudin Reply May 12, 2011 at 5:29 pm

          Adam,

          Good question. This is not my area of expertise…probably a better question for a SQL dba or newsgroup. However, I just tested your scenario and got the same exact results you are seeing. I was able to fix it by giving my GP SQL user access to the non-GP database and granting it SELECT access to the table in the view. Hope that helps get you on the right track.

          -Victoria

  3. Tanuja Reply February 14, 2011 at 8:19 am

    Hi,

    I want a report which is give me oppening balance for all account.
    Please guide for this.

    Thanks & Regards,
    Tanuja

  4. Vic Reply December 30, 2010 at 1:28 pm

    Hi Victoria

    I need to pull all the records from a static table we created for a certain list of parts name x_CS_INV_LIST.

    I have this table joined with 2 others IV00102 and POP10110 to gather inventory qtys and next date the item is due in. The query below is only returning 104 records there should be 300. any ideas? thanks as usual you rock!!!

    SELECT DISTINCT
    dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
    < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END AS Qty_Avail, MIN(DISTINCT CONVERT(VARCHAR(10),
    dbo.POP10110.PRMDATE, 101)) AS ETA
    FROM dbo.IV00102 LEFT OUTER JOIN
    dbo.x_CS_INV_LIST ON dbo.IV00102.ITEMNMBR = dbo.x_CS_INV_LIST.Code RIGHT OUTER JOIN
    dbo.POP10110 ON dbo.x_CS_INV_LIST.Code = dbo.POP10110.ITEMNMBR
    WHERE (dbo.POP10110.POLNESTA 5) AND (dbo.IV00102.LOCNCODE = ‘WHSE’) AND (dbo.POP10110.POLNESTA 6)
    GROUP BY dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
    < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END

    • Victoria Yudin Reply December 30, 2010 at 2:55 pm

      Hi Vic,

      Just want to make sure I understand your logic, you only want to show items that have a PO and where the line status of not closed or canceled? (I am guessing there is a between the POLNESTA and the numbers in the WHERE clause – it didn’t seem to copy properly in the comment.) Or do you want to show all items that are in inventory, whether or not there is a PO for them? How are you determining that there should be 300 records?

      Also, if you’re filtering based on PO line status, why not also filter out received? Wouldn’t those already be in the inventory quantities?

      And not too critical in this case, but you don’t need both a DISTINCT and a GROUP BY – they are redundant. You need a GROUP BY here because of the date calculation, so you can get rid of DISTINCT.

      -Victoria

      • Vic Reply December 30, 2010 at 4:38 pm

        Hi Victoria

        I need all the records from x_CS_INV_LIST table that is where the 300 comes from. That is a table we made for a specific group of customers

        Vic

        Thanks for the advice about distinct and group by and Recieved

      • Vic Reply December 30, 2010 at 4:44 pm

        So yes i want to show all items that are in inventory, whether or not there is a PO for them if they are in x_CS_INV_LIST

        thanks so much for all your help!

        Happy new Year BTW!!!!!

        • Victoria Yudin Reply December 31, 2010 at 6:50 am

          Hi Vic,

          Try the code below. I changed the type of join on the PO table to a left outer join and also moved the filter on the PO line status to the join (from the WHERE clause) – both of those were restricting your results to only return items that are on POs. I also changed it to only bring in line items that were not received, canceled or closed…if you want to change it back to what you had (change the < 4 to < 5 for the POLNESTA).
          SELECT
          x_CS_INV_LIST.Code, x_CS_INV_LIST.[Desc],
          CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) < 30
          THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END AS Qty_Avail,
          MIN(DISTINCT CONVERT(VARCHAR(10),
          POP10110.PRMDATE, 101)) AS ETA
          FROM IV00102
          LEFT OUTER JOIN
          x_CS_INV_LIST ON IV00102.ITEMNMBR = x_CS_INV_LIST.Code
          -- return all items, whether they are on a PO or not
          LEFT OUTER JOIN
          POP10110 ON x_CS_INV_LIST.Code = POP10110.ITEMNMBR
          AND POP10110.POLNESTA < 4 --excludes received, canceled or closed
          WHERE IV00102.LOCNCODE = 'WHSE'
          GROUP BY x_CS_INV_LIST.Code, x_CS_INV_LIST.[Desc],
          CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) < 30
          THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END

          Happy New Year!
          -Victoria

          • Vic Reply December 31, 2010 at 11:02 am

            THANK YOU!!!! As usual youe solution worked, the only thing I had to add was a Is not Null on the item code…..Your the best!!!!

            SELECT dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
            < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END AS Qty_Avail, MIN(DISTINCT CONVERT(VARCHAR(10),
            dbo.POP10110.PRMDATE, 101)) AS ETA
            FROM dbo.IV00102 LEFT OUTER JOIN
            dbo.x_CS_INV_LIST ON dbo.IV00102.ITEMNMBR = dbo.x_CS_INV_LIST.Code LEFT OUTER JOIN
            dbo.POP10110 ON dbo.x_CS_INV_LIST.Code = dbo.POP10110.ITEMNMBR AND dbo.POP10110.POLNESTA < 4
            WHERE (dbo.IV00102.LOCNCODE = 'WHSE')
            GROUP BY dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
            < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END
            HAVING (dbo.x_CS_INV_LIST.Code IS NOT NULL)

  5. Mark Reply December 29, 2010 at 4:17 pm

    Victoria,

    I realize what I am about to ask is probably rudimentary but I’ll ask anyway. Two questions:

    I have built a Smartlist and arranged everything as I wanted it, saved it, closed GP and went home. Came back in this morning and while the smartlist I created the day before was still there, the results were not. Does it make a difference whether I pull from a SQL table or Dynamics table? Cannot figure out where my info went.

    My smartlist are duplicating data. I created one where I could look at a purchase order and see the sales order associated (essentially SOP_POP link). When I run the Smartlist, items are repeating 4, 5, 6,….times dependent upon the number of total items on the purchase order.

    I appreciate your time and expertise.

    Sincerely,
    Mark

    • Victoria Yudin Reply December 29, 2010 at 5:38 pm

      Hi Mark,

      Not rudimentary at all…good questions.

      Question 1 – the data SmartLists show will always refresh to show current results – there is no saving the actual data, only the report layout. So for example, if you create a SmartList to show all unposted transactions in the GL and right now there are 20 of them, you will get 20 results. If someone posts all of those transactions and you open the SmartList again (or refresh it), you will have no results. Depending on the data, of course, it may never change – for example if you are reporting on all GL transactions in 2005 and you have closed all your years through 2009, it’s highly unlikely that 2005 data will change. If I am misunderstanding your question, please write back with more detail. There should be no difference between using a SQL table and a GP table – they are all really SQL tables. The GP tables will have user friendly names already populated, which might be more convenient, depending on what you’re doing.

      Question 2 – duplicate data usually (but not always) means you may have linked tables in a way you didn’t intend or didn’t link on enough fields. Without seeing your SmartList setup, it is difficult to say for sure. The SOP-POP link is done by line item, so I would expect to see multiple lines for any PO or SOP order that has multiple linked lines, however, if you’re seeing that, but multiplied by the number of lines, then you may need to take a closer look at your table links. If you want to write back with your links or your code, I can see if I can help determine what is causing the duplication.

      -Victoria

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 948 other followers

%d bloggers like this: