Run non-SELECT SQL statements via ADO from NAV codeunit

rsaritzkyrsaritzky Member Posts: 458
edited 2014-12-22 in SQL General
Hi,

I have a series of custom views which are used as linked tables in NAV. Of course, each view has to be created for each company in NAV. So in SQL, I have a script with something like this in the development database
DROP VIEW [DevCompanyA$v_Customer Balance View]
GO
CREATE VIEW [DevCompanyA$v_Customer Balance View] as
SELECT <.....>
GO
DROP VIEW [DevCompanyB$v_Customer Balance View]
GO
CREATE VIEW [DevCompanyB$v_Customer Balance View] as
SELECT <.....>
GO
DROP VIEW [DevCompanyC$v_Customer Balance View]
GO
CREATE VIEW [DevCompanyC$v_Customer Balance View] as
SELECT <.....>
GO

Now, I have TEST and LIVE databases where the views have to be created, but the company names are different, e.g. instead of DEVCompanyA it is TestCompanyA and LiveCompanyA, so the scripts are replicated.

There are many views, not just one.

Someone suggested that we create a codeunit in NAV to execute these scripts. I'm familiar with ADO programming to execute SELECT statements and update records that way, so I thought I'd give it a shot.

I first tried a very simple script:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'DevCompanyA$v_Customer Balance View') DROP VIEW [DevCompanyA$v_Customer Balance View]
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'DevCompanyA$v_Customer Balance View') DROP VIEW [DevCompanyB$v_Customer Balance View]
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'DevCompanyA$v_Customer Balance View') DROP VIEW [DevCompanyC$v_Customer Balance View]

I loaded the above script into a text string, created an ADO connection "conn"and

_connString := 'Provider=SQLOLEDB;Data Source=<servname>;Initial Catalog=<dbname>;<credential string>';
conn.Open(_connString);
<build _sqlString>;
conn.Execute(_sqlString);

The error that is displayed is the following:

Microsoft Dynamics NAV Classic
This message is for C/AL programmers:

The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message:
Incorrect syntax near '='.

I also tried passing just one command:
DROP VIEW [DevCompanyA$v_Customer Balance View]

- I received the same error message

I've found some references that "Incorrect syntax near '='" means an invalid SQL command, but the same exact command executes fine within SSMS.

We're running NAV2009 on SQL Server 2012 SP2.

Has anyone tried this, or has anyone tried, say running a script from within a NAV codeunit by passing a script to and executing sqlcmd or via a Powershell script? Any real examples of such?

Thanks
Ron

Comments

  • rmv_RUrmv_RU Member Posts: 119
    There are two options to run sql scriptis:
    1. Using ADO in new sql transaction
    2. Using SQL triggers in same transaction

    Second way is more preferable for data modification.
    Looking for part-time work.
    Nav, T-SQL.
  • rsaritzkyrsaritzky Member Posts: 458
    As I mentioned, I'm not doing any data modification in my script. What I want to do is to send a series of "DROP VIEW" and "CREATE VIEW" commands to SQL from a NAV codeunit. Has anyone ever tried this / accomplished this successfully?

    I've tried sending a SQLCMD statement via the workstation shell, but it hangs and doesn't execute properly.

    Ron
    Ron
  • ara3nara3n Member Posts: 9,248
    I haven't used ado recently, but if I remember there were different command types.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,248
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • keomakeoma Member Posts: 11
    there you have a simple example for using ADO in c/al.
    http://moxie4nav.wordpress.com/2014/12/ ... nt-in-cal/
    regards
  • rsaritzkyrsaritzky Member Posts: 458
    Thanks everyone for the replies.

    The examples in the referenced blogs/articles either execute a SELECT or an execute of a Stored Procedure. I have code that does both of those things. But I get the error reported above when I try to execute a "CREATE VIEW" statement.

    But I might try creating a Stored Procedure that does the "CREATE VIEW" and pass the string of commands in the view - that's not a half-bad idea to try.

    Thanks for the links.
    Ron
  • FDickschatFDickschat Member Posts: 380
    I know of such a solution in a highly customised DB.

    The view and the NAV object representing the view is stored in a record. You can
    • Import the sql script from a text file
    • Export the sql script to a text file
    • Drop the view in a single company or in all companies
    • Create the view in a single company or in all companies
    • Import the NAV object from a fob
    • Create the NAV object directly in the DB

    Don't forget to always convert ObjectNames/CompanyNames with a
    CONVERTSTR(String_p, './\', '___')
    

    If you are interested I can look it up and let you know more details.
    Frank Dickschat
    FD Consulting
  • keomakeoma Member Posts: 11
    hi rsaritzky,

    i've developed a solution for using "create view" with ado in c/al.
    follow http://moxie4nav.wordpress.com/2014/12/ ... eate-view/

    cheers
    regards
  • rsaritzkyrsaritzky Member Posts: 458
    Thank you for the sample code. It is almost identical to the code I already developed, but there are a couple of extra statements that I will definitely try to see if it does the trick. I will post my results here.

    Thanks again.
    Ron
Sign In or Register to comment.