25 Replies Latest reply on May 7, 2015 11:22 AM by David Herbert

    Queying SQL Database Basics

    Michael O'Dell
      Hi,
      I am trying to query our SQL Database in PDMWorks Enterprise to get information on files, specifically if they are checked in our out. I have no DB experience but have been looking at samples all around to see the basics. How would I get information on a file if it is checked in or out? I want to use this in conjunction with the report generator.

      Thanks.
        • Queying SQL Database Basics
          Joy Garon
          Michael -

          You don't need to use a query. You can do it with a simple search. (see image)

          Search the knowledge base for solution S-013369. It will describe how to create reports. You will need knowledge of T-SQL.

          Joy
            • Queying SQL Database Basics
              Michael O'Dell
              Joy,
              What I wanted to do with the checking if it is checked in or out is putting that in a report, I do not know the variable to use that it is stored in, in the table. I was going to modify the example report that is in the PDMWorks install file and include the checked in/out status.

              Thanks for your help,
              Michael
                • Queying SQL Database Basics
                  Joy Garon
                  Try this....

                  select filename
                  from documents
                  where userid <> 1 and deleted <> 1
                    • Queying SQL Database Basics
                      Michael O'Dell
                      Thanks for your reply,
                      Like I said I am fairly new to SQL coding I am posting the method that I am modifying that was made by SolidWorks to incorporate the checked state. I am not sure where to put your code within that method and what variables are used.

                      Your code is highlighted

                      @[DocumentsInWorkflowstate]
                      §Name [Documents in selected workflow state]

                      §Company [SolidWorks]

                      §Description
                      [This query lists all documents in a selected state and to which the user has access rights.]

                      §Version [1.1]

                      §Arguments
                      [
                      String FileName [N] [Enter file names. Use "%" for wildcard. E.g. "in%.%"]
                      ProjectID StartProjectID [1] [Enter start folder. E.g "$\Top folder", or browse for folder.]
                      StatusID CurrentStatusID [1] [Select status]
                      UserID TheUserID[S] [Select user]
                      ]

                      §Sql
                      [
                      Declare @iStartFolder Int
                      Declare @StatusID Int
                      Declare @UserID Int

                      Set @iStartFolder = {StartProjectID}
                      Set @StatusID = {CurrentStatusID}
                      Set @UserID = {TheUserID}

                      select filename
                      from documents
                      where userid <> 1 and deleted <> 1


                      Select Doc.DocumentID, P.Path + Doc.Filename as 'Document Name', Doc.LatestRevisionNo as 'Latest Version'
                      From Projects P, DocumentsInProjects Dip, Documents Doc
                      Where P.Path like Replace(Replace(Replace( (Select Path From Projects Where ProjectID = @iStartFolder )
                      ,'[','[[]'),'_','[_]'),'%','[%]') + '%' And
                      P.ProjectID = Dip.ProjectID And
                      Doc.DocumentID = Dip.DocumentID And
                      Dip.Deleted = 0 And
                      Doc.CurrentStatusID = @StatusID And
                      {Doc.Filename, FileName, LIKE, OR} And
                      Exists ( SELECT ProjectID
                      FROM UserProjectRights
                      WHERE ProjectID = P.ProjectID AND
                      UserID = @UserID And
                      Type & 1 = 1
                      Union
                      ( SELECT Gpr.ProjectID
                      FROM GroupProjectRights Gpr, GroupMembers Gm
                      WHERE Gpr.ProjectID = P.ProjectID AND
                      Gpr.GroupID = Gm.GroupID And
                      Gm.UserID = @UserID And
                      Gpr.Type & 1 = 1 ) )

                      ]
                • Queying SQL Database Basics
                  Michael Dekoning
                  Michael,

                  For checked out (locked) files use this.

                  select filename from documents where len(lockpath) > 0 and deleted = 0

                  For checked in files,

                  select filename from documents where len(lockpath) = 0 and deleted = 0

                  Be careful though, this can potentially return thousands of records!

                  Mike
                    • Queying SQL Database Basics
                      Joy Garon
                      Michael -

                      You can't just put SQL queries in the enterprise report tool. Please look at the solution I refered you to earlier.

                      The report should look more like this:


                      /******************* ********************/

                      @[CheckedOut]
                      §Name [List Checked out documents]

                      §Company [SolidWorks]

                      §Description
                      [This query lists all checked out documents document]

                      §Version [1.1]

                      §Arguments
                      [

                      ]

                      §Sql
                      [
                      SELECT Documents.Filename AS [File Name], Users.Username AS [Checked Out By]
                      FROM Documents INNER JOIN
                      Users ON Documents.UserID = Users.UserID
                      Where Documents.Userid <> 1 AND Documents.Deleted <> 1

                      ]

                      /******************* ********************/
                        • Queying SQL Database Basics
                          Michael O'Dell
                          Thanks a lot for all your answers and help. I am new to PDMWorks as well as SQL so it's been a little rough figuring things out. I am sure I will be back.
                            • Queying SQL Database Basics
                              Michael O'Dell
                              Just a quick follow up. Is there any quide besides the one that you directed me to Joy? Specifically the variables within a table and what not. I have a few more simple tasks I need to complete such as the number of items in a program and how many items have been released.
                                • Queying SQL Database Basics
                                  Joy Garon
                                  Hi Michael -

                                  Not really. You need to use the SQL Server Management Studio to examine the database tables and columns.
                                  If you have Visio (2003 SP3) you can create an entity relationship diagram (table map) by using the Reverse engineer feature under tools.

                                  Regards,
                                  Joy
                                    • Queying SQL Database Basics
                                      Michael O'Dell
                                      I downloaded the Server Management tool and I have been browsing around the Vault which is set up as default. I also read through a couple hours of tutorials on SQL from www.w3schools.com (Great resource for anyone needing help in almost any language). What I am having trouble finding is in which table is the state of a document held. Such as I want to find if a document is in progress, released, etc. Any clues?

                                      Thank you again for all your help,
                                      Michael
                                        • Queying SQL Database Basics

                                          select documents.Filename, status.Name
                                          from documents
                                          join status
                                          on status.StatusId = documents.currentstatusid
                                            • Queying SQL Database Basics
                                              Joy Garon
                                              This one will give you the project paths as well...

                                              SELECT P.Path As 'Project Path', D.Filename AS 'File Name', S.Name AS 'Status Name'
                                              FROM Projects AS P INNER JOIN
                                              DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID INNER JOIN
                                              Documents AS D ON DP.DocumentID = D.DocumentID INNER JOIN
                                              Status AS S ON D.CurrentStatusID = S.StatusID
                                              Where (S.Name LIKE '%') AND D.ObjectTypeID <> 0
                                              Order By S.Name ASC
                                • Re: Queying SQL Database Basics

                                  Thought I'd pass this on, as I haven't seen reference to it anywhere....

                                   

                                  The easiest method I have found to create SQL Queries is to let EPDM build them for me.

                                   

                                  1) Create the column set for the fields you want returned

                                  2) Run the search

                                  3) Change the registry setting:

                                  Create a new DWORD value called DebugSQL under HKEY_LOCAL_MACHINE\SOFTWARE\SolidWorks\Applications\PDMWorks Enterprise Set it to 1 When running a search, the search query will be created in a file called c:\search.txt

                                  4) Paste the contents of c:\search.txt into a new query.

                                   

                                  This has made authoring reports in Crystal Reports a breeze.

                                   

                                  Every time a search is run,  c:\search.txt will be updated with the new query.

                                    • Re: Queying SQL Database Basics
                                      Great tip David!
                                      • Re: Queying SQL Database Basics
                                        Kent Keller

                                        I'm totally freaked out by how you knew that!  Awesome!

                                          • Re: Queying SQL Database Basics
                                            Tony Greising-Murschel

                                            Get back to work Kent

                                             

                                            How's EPDM treating you?

                                              • Re: Queying SQL Database Basics
                                                Kent Keller

                                                Actually, pretty decent.  The database normalization is extreme and there is no such thing as a "Simple SQL Query".  However, EPDM has done a nice job working out their database scheme.  No SQL database customization is needed to add variables.  Adding variables is an "Out of the Box" proceedure.  API is all .NET!  No .lst files, sys files, par files, txt files, dbw files and .vbs file to debug.  Nice referencing structure (even if documents are suppressed or in another configuration).  Item management is awesome!  (We don't engineer files, we engineer items; conceptual part numbers, not the files that define them).  Workflow is much more robust and flexible.  It is way too easy to over secure the permissions (which, in my opinion, is the number one reason why inexperienced PDM administrators fail in their implimentation).  I really wish EPDM would default to full permissions on everyone when defining workflows and the like.  The idea that you "lock it down and give access as needed" is bunk!  Start with everything wide open and only lock it down when absolutely necessary.  Security is not free!  Security is a necessary evil, but when it is not necessary, it is just plain evil. Probably the number one form of waste in engineering companies is the time it takes to work around uncessary security.

                                                 

                                                In EPDM, most of the complexity is hidden from the user, which makes my job harder, but the user's job easier.  I actually like that.   EPDM seems a lot more consistent and intuitive to the user.

                                                 

                                                My biggest concern is, due to the normalization level of the database, that "minor" problems can have catastrophic consequences in time and money before resolved.  There are no simple "backend hack" solutions that can be trusted.

                                                 

                                                I guess I have a lot of homework to do

                                                 

                                                Kent

                                            • Re: Queying SQL Database Basics
                                              Tim Webb

                                              Hi David,

                                               

                                              I have tried this tip to debug my sql queries. I am not sure what I'm doing wrong but there is not a search.txt file or any other text file that gets created.

                                               

                                              I followed your instructions to the letter. Is there something I could have done wrong?

                                               

                                              Thanks

                                              Tim

                                               

                                              EPDM 2011 SP2.0

                                              SQL Server 2008

                                                • Re: Queying SQL Database Basics
                                                  Brandon Barnes

                                                  I know this is an old thread but I thought it might help since there's not much info on it anywhere. I was having the same trouble as Tim. I figured out that I had to right-click on the PDM Search and click Run As Administrator. The problem was security settings with Windows 7. It won't let you save a file to the root directory on your hard drive unless you do this. There is also an option in the shortcut properties that will allow you to always run it as admin.

                                                    • Re: Queying SQL Database Basics
                                                      Scott Hayden

                                                      Hi Brandon-


                                                      I am having the same issue as Tim whereas I have followed David's instructions fully and I am not getting a search.txt output anywhere.  I have also tried right-clicking the PDM search icon at the upper-right corner inside my vault, as you've instructerd, but this does not bring up any menu.  Are you able to do this in your vault, or does this need to be done somewhere else?  Also not sure what you mean about the option in the shortcut properties...what shortcut properties?  Also, I believe that I am setup as the admin of my machine, so I am not sure if that is the issue.

                                                       

                                                      Do you have any other tips to creating the search.txt file?

                                                       

                                                      Thank you,

                                                      Scott

                                                       

                                                      EPDM 2014

                                                      SQL Server 2008

                                                      Windows 7

                                                        • Re: Queying SQL Database Basics
                                                          Brandon Barnes

                                                          Scott, try right-clicking the "Enterprise PDM Search" icon on your desktop or through the Start menu. There should be an option that says "Run as administrator". Click that and run your search. If you created the DebugSQL registry key per David's instructions, it should create a "search.txt" file in the root of your "C:" drive. For security purposes, Windows 7 doesn't let programs save anything to the root folder on the hard drive without the user executing administrative privileges.

                                                           

                                                          Run as admin.jpg

                                                            • Re: Queying SQL Database Basics
                                                              Scott Hayden

                                                              Hi Brandon-

                                                               

                                                              Thank you, that worked to create the search.txt file.  I had to first locate the search executible file, which I found here:  C:\Program Files\SolidWorks Enterprise PDM.  I created a desktop shortcut and was able to right-click and "Run as Administrator".

                                                               

                                                              All good there, however I am still not able to replicate the query search when I copy-paste the search.txt contents into a New Query in SQL Management Studio.  Here are the steps I have taken:

                                                               

                                                              1. I have created a column set of fields that I would like the search to return, via ePDM Administrator.
                                                              2. I opened the "Complete Search" search card (which is Out of the Box with ePDM) and saved-as a new search card.  I called it "ePDM Query Search".
                                                              3. I open up the new card in the Card Editor and change the Results Column Set to the new column set that I had previously set up.  Save the new card.
                                                              4. I go back to my desktop and right-click and run the ePDM search as an administrator.
                                                              5. I navigate to the "ePDM Query Search", set up my search strings (Look in location, Name, Workflow), and start the search.  I get results in the ePDM search window, and also the search.txt file is created in the C:/ directory.
                                                              6. I open the search.txt file, which shows quite a long and complicated set of SQL coding.
                                                              7. I highlight ALL of the code.  I open up SQL Management Studio and open up a New Query. Paste the code into the dialog box and get this error:

                                                               

                                                              "Msg 208, Level 16, State 1, Line 29

                                                              Invalid object name 'dbo.Src_FindProjectsWithReadRights'."

                                                               

                                                              Are you able to copy and paste the search.txt text directly into a new query within SQL Management Studio and output the same results as you did with the ePDM search?    In the big picture I am hoping to accomplish two things by doing this:  1)  I would like to use this code to set boilerplate Report Generator queries within ePDM.  2) I want to set up SQL queries within DriveWorks to create tables of information that our designers can use.

                                                               

                                                              Thank you again!

                                                              Scott

                                                      • Re: Queying SQL Database Basics
                                                        David Herbert

                                                        After five years, I found this post while I was researching data access. It's worth noting that x64 flavors of Windows require that regedit32.exe be used to edit the registry for this tweak to work.