SBX - Search With Button

SBX - Forum Post Title

Worksheet 51 CREATE VIEW error

Microsoft Dynamics RMS Forum

andrzej@texo.ca asked a question on 3 Jun 2014 1:50 PM
My Badges

Question Status

Unanswered

I created Worksheet 51 wit the following SQL :

CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance FROM AccountReceivable LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID WHERE Customer.AccountNumber = 012012 GROUP BY CustomerID',1

I got the following error when HQ Client connected to execute this worksheet:

<<ExecuteCommand>> Error -21472179000: Incorrect syntax near the keyword 'SELECT'

Locally it works in SO POS, so are there any restrictions when creating SQL query in Worksheet 51?

Reply
Fernando responded on 3 Jun 2014 6:06 PM
My Badges

Single quote at the end of CustomerID'...?

Reply
andrzej@texo.ca responded on 4 Jun 2014 10:55 AM
My Badges

Originally it is like this:

CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance FROM AccountReceivable LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID WHERE Customer.AccountNumber = 012012 GROUP BY CustomerID

and it does not work

Reply
Fernando responded on 4 Jun 2014 5:24 PM
My Badges

If you can create the view when connecting to the SO database, then it may be an issue with permissions; ie, the worksheet 51 connection may not be using the necessary credentials to create a view. Try using dbo.ARTemp to qualify the view name; never tried doing these types of db object creation with a 51 worksheet, so I'm going out on a limb.  This may help develop this further msdn.microsoft.com/.../ms187956(v=sql.90).aspx

Reply
Craig Harris responded on 3 Jul 2018 6:07 PM
My Badges

I realize this is an old thread but I am wondering if you were able to create a view with the WS 51?   I am trying to do similar but get a syntax error at SELECT .  The sql works in SMSS and Store Ops admin. Any feed back would be appreciated. I have tried the dbo. naming but it made no difference.

Craig

Reply

SBX - Two Col Forum

SBX - Migrated JS