Truncate and/or Insert with SQL Non-Query Activity

Permanently deleted user

06 Juni 2017 17:13

Afternoon,

 

I need to be able to truncate and then insert into a specific table in our SQL database using the SQL Non-Query activity.

 

I've tested out both the truncate table and insert sql statements in SQL Server Management Studio with the same account that is used in the SQL workflow activities, and both are successful.

 

But the error I am receiving states that:

 

Cannot find the object "GRW_Test" because it does not exist or you do not have permissions.

 

Any suggestions?

 

Walter

 

 

 

0

• 

  Permanently deleted user

  06 Juni 2017 17:42

  Hi Walter,

   

  Could you please post your connection string (with the username, password, and server names changed), so I can take a look?  Based on the error message, it sounds like the workflow can't connect to the database object.  

   

  You might also find some clues in the SQL log files.

   

  Thanks!

   

  Danny

  0
• 

  Permanently deleted user

  06 Juni 2017 17:51

  Daniel.

   

  Thanks for responding. My connection string is below.

   

  Server=xxxxx;Database=xxxx;User Id=xxxxx;Password=xxxxxx

   

  Where do you find the SQL log files?

   

  Walter

   

   

  0
• 

  Permanently deleted user

  06 Juni 2017 17:58

  Thanks Walter!  Your connection string looks OK from a syntax point of view, although you may need to play with the username/server values (they may need to be fully qualified if they are not, for example server.domain.com instead of just server, and user@domain.com instead of just user)

   

  Here's an article from Microsoft about SQL server logs:  https://docs.microsoft.com/en-us/sql/tools/configuration-manager/viewing-the-sql-server-error-log

   

  If you're still having trouble, I'd suggest opening a support case.  Issues like this are usually easier to troubleshoot if we can see firsthand what's going on.

   

  I hope this helps!

   

  Thanks,

   

  Danny

  0
• 

  Permanently deleted user

  06 Juni 2017 18:03

  Daniel,

   

  Thanks. I'll give your suggestions a try.

   

  Walter

  0
• 

  Permanently deleted user

  06 Juni 2017 18:18

  Daniel,

   

  In another posting, someone mentioned that: 

   

  The connection string needs to be added to the existing "REST Elements\REST\web.config" and "Workflow Designer\Geocortex.Workflow.Designer.exe.config" files.

   

  Can you please provide an example of the lines I would need to add to both the web.config and and WorkFlow.Designer.exe.config?

   

  Walter

  0
• 

  Permanently deleted user

  06 Juni 2017 18:34

  Hi Walter,

   

  You only need to add your connection strings to the web.config files if you are planning on encrypting them.  I don't think this will have any impact on the connection itself.  I would suggest focusing on getting them working in the default non encrypted state, and then look at moving them into the *.config files.

   

  Here are the instructions though:

   

  https://support.geocortex.com/essentialsGSCkba?sub-nav=kba&main-nav=essentials&/feedtype=SINGLE_ARTICLE_DETAIL&criteria=BESTANSWERS&id=kA360000000Cia2CAC

   

  Thanks!

   

  Danny

  0
• 

  Permanently deleted user

  06 Juni 2017 19:41

  Thanks Danny

   

  Walter

  0
• 

  Permanently deleted user

  07 Juni 2017 18:28

  Danny,

   

  The issue I was having with the SQLNonQuery regarding the inability to truncate and/or insert had nothing to do with the connection string I was using. Rather, the account I was using in my connect string did not have the following database permissions:

   

  1. Alter

   

  2. Delete

   

  3. Insert

   

  4. Select

   

  5. Update

   

  After these were added, the truncate and insert worked as expected.

   

  Walter

   

   

  0
• 

  Permanently deleted user

  07 Juni 2017 18:33

  That's great Walter!  Thank you for posting your solution too :)

   

  Cheers,

   

  Danny

  0

Du måste logga in om du vill lämna en kommentar.