Run non-SELECT SQL statements via ADO from NAV codeunit

rsaritzky

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

rmv_RU

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.

rsaritzky

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

ara3n

I haven't used ado recently, but if I remember there were different command types.

ara3n

Here is a link

http://dynamicsuser.net/blogs/waldo/arc ... -side.aspx

keoma

there you have a simple example for using ADO in c/al.
http://moxie4nav.wordpress.com/2014/12/ ... nt-in-cal/

rsaritzky

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.

FDickschat

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.

keoma

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

rsaritzky

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.