Excel SQL

[rabso]

Hi Guys, im confused and I hope you can spread some light on this.

I have a sql query that runs in sql studio manager, but doesnt run in excel query manager. I have some queries that do and some that dont run in excel. Are there limitations?

Any ideas why?

Richard Baker

 

[Richard Schollar]

Depending on what driver is being used will determine what SQL commands are available to you. Care to post any of the offending queries that aren't working (it may well not be immediately obvious to any of us why it is failing but it wouldn't hurt)?

 

[rabso]

Hi Richard, I am using ODBC, this is my query.

SELECT
P.ProdId AS 'Finishing Prod Id',
I.OCS_ArtworkNb AS 'Proof Nb',
ID.InventSerialId AS 'Proof Serial Nb'
FROM
ProdTable AS P WITH(NOLOCK) INNER JOIN InventTable AS I WITH(NOLOCK)
ON (P.ItemId = I.ItemID AND I.DataAreaId = 001)
OUTER APPLY (SELECT TOP (1) P2.ProdID, P2.InventDimId, P2.DataAreaId
FROM ProdTable AS P2 WITH(NOLOCK)
WHERE I.OCS_ArtworkNb = P2.ItemID
AND P2.DataAreaId = 003
ORDER BY P2.ProdId DESC) AS P2
INNER JOIN InventDim AS ID WITH(NOLOCK)
ON (P2.InventDimId = ID.InventDimID AND P2.DataAreaId = ID.DataAreaId) 
WHERE
P.DataAreaId = 003
AND P.ProdStatus <> 7
AND P.ProdPoolId = 'Finish'
ORDER BY
P.ProdId

I appriciate any help you can offer.

 

[Ruddles]

Does it not give you any sort of (useful) error message?

I don't know for sure but I think you need square braces around field names in Excel rather than apostrophes:-

SELECT
P.ProdId AS [Finishing Prod Id],
I.OCS_ArtworkNb AS [Proof Nb],
ID.InventSerialId AS [Proof Serial Nb]

 

[rabso]

Ruddles you are a genius, you have just solved an issue I have had for 2 years. It was the braces, thank you so much.

Regards,

 

[Ruddles]

"I've already told you more than I know."