OpenQuery With Large String?

Hi,

I declare a variable @.MdxSyntax as NVARCHAR(4000) to store MDX OpenQuery syntax on Store Procedure.

SET @.mdxSyntax =
'
SELECT * INTO ##BU01505100
FROM OPENQUERY
(MOJOLAP,
''
WITH

'')
'

EXEC sp_executesql @.mdxSyntax

But maybe the syntax too long, system response syntax unclosed!

So, I change @.MdxSyntax as NVARCHAR(MAX), but it still response syntax unclosed.

Why? It's the limit of OpenQuery or MDX?

Thanks for help!

Note:

OPENQUERY does not accept variables for its arguments.

You have to use the query as String values on OPENQUERY.

|||

ManiD,

Thanks for your reply!

But my point is no matter what I declare @.mdxSyntax as NVARCHAR(4000) or NVARCHAR(MAX),

the query result always response syntax unclosed. WHY?

OpenQuery Syntax Using Variables

I have an openquery statement with a parameter embeded as a variable:

declare @.product varchar(3)

set @.product= 'ABC'

select * from openquery(SomeServer,'

SELECT Description, Size

FROM Products

WHERE

Group = ''XY'' AND

Code = ''' + @.product + '''')

When I run it I get the following message:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '+'.

When I hard code the "Code" value, like so:

Code = ''ABC''')

...it works fine.

I am at a loss and would appreciate any help on this.

Thanks in advance

SQL Servant

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

Code Snippet

EXEC ('SELECT * FROM OPENQUERY(SERVER, ''SELECT * FROM TABLE WHERE Table_Id = ' + @.Table_Id_Str + ''')')

|||

Tried it... same error.

Thanks,

SQL Servant

Reply to --

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

The query sent to the server needs to have 'ABC' rather than "ABC".

I have used EXEC before and got the same error. Anyway, this query is part of an IF structure...

IF EXISTS (select * from openquery(...))

[do this ] ELSE [do that]

Thanks,

SQL Servant

Reply to -

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

|||

I have fixed the problem...

Apparently it is to do with scope and stuff...

If I put the query in a EXEC command then it works. So, the code becomes this:

EXEC('

select * from openquery(SomeServer,''

SELECT Description, Size

FROM Products

WHERE

Group = ''''XY'''' AND

Code = ''''' + @.product + ''''''')

')

In fact, I have put the entire IF structure (that this query is a part of) inside an EXEC command.

Over and out,

SQL Servant

|||

Wasn't that the idea of what I posted? ;-P

I think I deserve a star, hee hee

Anyway, please mark this thread as Answered

Glad it worked out for you

openquery

BOL says that 'OPENQUERY does not accept variables for its
arguments'. So, I tried using something like:
Declare @.qry varchar(1000)
Set @.qry = 'select * from OPENQUERY
(<linked_server_name>, ''<query>'')'
exec (@.qry)
Would this work? Thanks.Yes.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.|||That is called Dynamic SQL and should work if the final syntax comes out
correct.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.

OPEN XML QUESTION

DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandlerINT
DECLARE@.QuestionName TABLE(
IdINT IDENTITY(1,1),
[metatag][Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT*
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation7700
quality7701
I expect like this
Evaluation7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
Deva
You want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>

OPEN XML QUESTION

DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandler INT
DECLARE @.QuestionName TABLE (
Id INT IDENTITY(1,1),
[metatag] [Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation 7700
quality 7701
I expect like this
Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
DevaYou want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>