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 using parameters

I need a little help with using parameter values in the where clause
with the OpenQuery statement.
What would the syntax for the following be if 10 was a variable? And
does the syntax change depending on the datasource? In my case I am
querying an Oracle database. But I can't get it work on an Oracle or
Access database.
SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
WHERE deptno = 10'
Many thanks to the answer for this.
johnny
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenetJohnny
Did you create a linked server to the Oracle database?
BOL has some examples how to create a linked server to Oracle adatabase.
SELECT * FROM OracleServer.Database.dbo.Table WHERE blblbl
Does the aboe example work for you?
For Access database you can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Johnny" <myob@.beatles.com> wrote in message
news:PBoof.631530$_o.29628@.attbi_s71...
>I need a little help with using parameter values in the where clause
> with the OpenQuery statement.
> What would the syntax for the following be if 10 was a variable? And
> does the syntax change depending on the datasource? In my case I am
> querying an Oracle database. But I can't get it work on an Oracle or
> Access database.
> SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
> WHERE deptno = 10'
> Many thanks to the answer for this.
> johnny
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet

OPENQUERY UPDATE Syntax help needed

Hi All

I am updating a local table based on inner join between local table
and remote table.

Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

Thanks
RS(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

I am updating a local table based on inner join between local table
and remote table.
>
Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3
>
This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

UPDATE LocalTable
SET Field1 = B.Field1
FROM OPENQUERY(LINKEDSERVER,
'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

I would not really expect this to perform better.

Distributed queries are always difficult, but it's difficult to suggest
anything without further knowledge about the table. How big are the
two tables?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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 syntax for function

There is a very complex query where I'm trying to call a function from a
linked server with input paramaters from the local database. This is the
general idea:
select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
from table1 t1
and table t2
where ...
I get a syntax error that doesn't recognize t1 and t2. How should I fix thi
s?
Thanks,Openquery() (i.e. ad-hoc/pass through function) only takes literal strings.
So, it's not possible to pass in any parameters.
Also, it's not possible to call a remote user-defined function in sqlserver
(i.e. srv.db.dbo.udf() is not allowed). So, you would have to create the
function locally.
-oj
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||Lisa
Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
Table-Valued UDF?
Look at this technique written by Itzik Ben-Gan
CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
AS
BEGIN
RETURN(SELECT newinvid FROM OPENQUERY([server_name],
'SET NOCOUNT ON; DECLARE @.invid AS INT;
UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
SELECT @.invid AS newinvid;') AS O)
END
CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
AS
BEGIN
RETURN(
SELECT newinvid
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
INSERT INTO tempdb..Seq2 DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
END
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||I'm looking at more in line with your first function. but I have two
parameters (one an integer) and one a date that is being to the function tha
t
I want to use in the openquery statment. I can't get the sql right for it
though.
"Uri Dimant" wrote:

> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Stateme
nt
> Table-Valued UDF?
> Look at this technique written by Itzik Ben-Gan
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @.invid AS INT;
> UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
> SELECT @.invid AS newinvid;') AS O)
> END
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
>
>

OPENQUERY question

I'd like to use a linked server to a Setcim database to retrieve results fro
m
a procedure in the Setcim database.
The syntax used to call the view in Setcim is:
start record 'BSW1View'
which works when using the SQLplus tools for Setcim.
I was assuming I could OPENQUERY to call this record and then put the data
into a SQL Server database but I get syntax errors when I try various
combinations of the following:
SELECT *
FROM OPENQUERY(PRISM,'start record 'BSW1View'')
The issue appears to be the single quotes around the procedure name.
Can I do what I want using OPENQUERY? If not, what options are available?
Thanks in advance,
RaulDouble them (the inner apostrophes).
SELECT *
FROM OPENQUERY(PRISM,'start record ''BSW1View''')
AMB
"Raul" wrote:

> I'd like to use a linked server to a Setcim database to retrieve results f
rom
> a procedure in the Setcim database.
> The syntax used to call the view in Setcim is:
> start record 'BSW1View'
> which works when using the SQLplus tools for Setcim.
> I was assuming I could OPENQUERY to call this record and then put the data
> into a SQL Server database but I get syntax errors when I try various
> combinations of the following:
> SELECT *
> FROM OPENQUERY(PRISM,'start record 'BSW1View'')
> The issue appears to be the single quotes around the procedure name.
> Can I do what I want using OPENQUERY? If not, what options are available?
> Thanks in advance,
> Raul
>|||Your suggestion worked. The only problem is I got the following error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'start record 'BSW1View''. The OLE DB provider
'MSDASQL' indicates that the object has no columns.
I'll modify the procedure on the Setcim side and try again.
Thanks for the help,
Raul
"Alejandro Mesa" wrote:
> Double them (the inner apostrophes).
> SELECT *
> FROM OPENQUERY(PRISM,'start record ''BSW1View''')
>
> AMB
> "Raul" wrote:
>

OpenQuery Error?

Hi All,

I have an Openquery insert within a trigger. When i go to check the syntax it errors with the following message...

Error 403. Invalid Error for Data Type. Operator equals add, type equals varchar.

Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'

All variables are varchar except for @.KEYE which is Varbinary and this is the one that is causing the error on, because if I take it out the syntax is correct.

Any ideas why this occurs and how do I add a varbinary variable to the statement??

Regards
Anthonyyou are trying to concat string with varbin - this will not work. you have to convert to varchar before...|||Originally posted by msieben
you are trying to concat string with varbin - this will not work. you have to convert to varchar before...

How do I do this?? Can you show me an example.|||Originally posted by aljubicic
:
Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'
:


the code you posted will build the insert statement togeter and put it into @.TSQL2 as a varchar. so everything you put toghether needs to be varchar or to be converted to (either by the sql-server or by using "convert(". i don't know what you really want to do - but look alt sp_executesql in BOL. you can store your statement into varchar and use parameters, which will be replaced at runtime. so you don't have to push your values list into the varchar at all.

OpenQuery & Quotes Syntax

I'm trying to select records within a date range from a linked server. If I
don't include the WHERE part and it's variables, my sql retrieves all
records. I also can hard code the WHERE criteria like "loadID=500".
SQL returns an error on my below code saying"
Incorrect syntax near '+'
What am I doing wrong with my commas and plus syntax? It looks correct, but
doesn't like my plus syntax for some reason.
CODE ***********
declare @.dtStartDate datetime, @.dtEndDate datetime
set @.dtStartDate = GetDate()
set @.dtEndDate = '20060701'
SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM load
WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112) +
'''' + ' AND '
+ '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112) +
'''')On my system that string works fine. Using the current date for the
variables I get:
SELECT loadID, loadDate FROM load WHERE loadDate >= '20060515' AND
loadDate <= '20060515'
Are you certain that this is the line with the error?
Try to avoid building strings as parameters like this. Instead, assign the
string to a variable, and pass the variable as the parameter. You can
output the variable in order to make sure it contains the correct string.
You can then execute this string seperately in Query Analyzer to simplify
troubleshooting.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23jm8YzCeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> I'm trying to select records within a date range from a linked server. If
I
> don't include the WHERE part and it's variables, my sql retrieves all
> records. I also can hard code the WHERE criteria like "loadID=500".
> SQL returns an error on my below code saying"
> Incorrect syntax near '+'
> What am I doing wrong with my commas and plus syntax? It looks correct,
but
> doesn't like my plus syntax for some reason.
>
> CODE ***********
> declare @.dtStartDate datetime, @.dtEndDate datetime
> set @.dtStartDate = GetDate()
> set @.dtEndDate = '20060701'
> SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
> FROM load
> WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112)
+
> '''' + ' AND '
> + '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112)
+
> '''')
>|||SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM
load
WHERE
loadDate >= convert(char(8), getdate(), 112) +
AND loadDate <= convert(char(8), ''20060701'', 112)')
Could u please try with this?

OpenDataSource()

Hello, I am trying to connect to a JBase database via an Attunity Connect
ODBC machine DSN but I'm struggling with the syntax. The DSN is called
ClarityODBC and this is the syntax I'm using:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityOD
BC;').CLARITY.[PUBLIC].
1;TRANS]
I get the following error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "(null)".
Obviously, I am doing something wrong but I haven't used OpenDataSource
before and I am struggling to find samples for the above scenario ie using a
System DSN and I'm not even sure if I should be using SQLOLEDB
Any help is greatly appreciated.
Many thanks
MarkHi, Mark,
From your description, I understand that:
You encountered the error :
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
When you executed the distributed query:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityOD
BC;').CLARITY.[PUBLIC].
1;TRANS]
If I have misunderstood, please let me know.
The error should be caused by not specifying authentication information.
If you would like to use Windows authentication, please add "Integrated
Security=SSPI" to the init_string parameter, for example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000
;Integrated
Security=SSPI').Northwind.dbo.Categories
If you use SQL Authentication, please add "User
ID=<userid>;Password=<password>" to the init_string parameter. It is
recommended that your password uses a strong name. For example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000
;User
Id=sa;Password=Password!01').Northwind.dbo.Categories
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

OpenDataSource()

Hello, I am trying to connect to a JBase database via an Attunity Connect
ODBC machine DSN but I'm struggling with the syntax. The DSN is called
ClarityODBC and this is the syntax I'm using:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityODBC;').CLAR ITY.[PUBLIC].[TRANS]
I get the following error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "(null)".
Obviously, I am doing something wrong but I haven't used OpenDataSource
before and I am struggling to find samples for the above scenario ie using a
System DSN and I'm not even sure if I should be using SQLOLEDB
Any help is greatly appreciated.
Many thanks
Mark
Hi, Mark,
From your description, I understand that:
You encountered the error :
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
When you executed the distributed query:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityODBC;').CLAR ITY.[PUBLIC].[TRANS]
If I have misunderstood, please let me know.
The error should be caused by not specifying authentication information.
If you would like to use Windows authentication, please add "Integrated
Security=SSPI" to the init_string parameter, for example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000;Integrate d
Security=SSPI').Northwind.dbo.Categories
If you use SQL Authentication, please add "User
ID=<userid>;Password=<password>" to the init_string parameter. It is
recommended that your password uses a strong name. For example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000;User
Id=sa;Password=Password!01').Northwind.dbo.Categor ies
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Opendatasource

What is the syntax for using parameters in an opendatasource query? I tried using the below:

SELECT top 10 *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.22.10.78;User ID='+@.user+';Password='+@.pword
).Northwind.dbo.Categoriesad-hoc (i.e. openquery/openrowset/opendatasource) only accepts string literals. You have to formulate your entire opendatasource query and execute it.

e.g.
set @.sql='select *
from opendatasource(...)'

exec(@.sql)|||Nothing really happens when I try to exec the following, Is my syntax correct?:

Declare @.user as varchar(25)
Declare @.pword as varchar(25)
Declare @.Cstring as varchar(500)

Set @.cString = 'SELECT top 10 SOPNUMBE, TRXDATE, LNITMSEQ, [DB_ID], AccountId, XTNDPRCE
FROM OPENDATASOURCE(
' + 'SQLOLEDB' + ',' +
'Data Source=server1;User ID=' + @.user + ';Password=' + @.pword + '
).dw1.dbo.Table1'

exec (@.cstring)|||Wasn't entering the username and password. Is there a way to make this an Insert statement?|||insert into what? like this?

--set @.sql='your select query'

insert tb
exec(@.sql)

opendatasouce and ingres database

Dear all,

I want to connect to an Ingres Database in Sql Server 2000 using OPENDATASOUCE

But I don't know the syntax about OPENDATASOURCE when connect to an Ingres Database?

Thanks you

Hi,

The OPENDATASOURCE syntax is quite simple: OPENDATASOURCE (provider_name, init_string). In order to connect to the Ingres this way, you need an OLE DB provider for Ingres. Microsoft does not ship one, but there're 3rd party ones available on the market. The corresponding vendor documents the provider and all the relevant connection string tokens, that is the right place to look for this information.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

OPen xml rowpattern probs!

Hi All,

I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.

Definitely doing something wrong here, please advise!

DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'

DECLARE @.docHandle int

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc

SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')

EXEC sp_xml_removedocument @.docHandleDon't worry all sortedsql

Open Xml

Hi All,

I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.

Definitely doing something wrong here, please advise!

DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'

DECLARE @.docHandle int

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc

SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) 'http://http://DVName',
DvcodeNo int 'http://http://DvcodeNo',
CustSurveyNo int 'http://../CustSurveyNo',
ClaimGroupType varchar (20) 'http://../ClaimGroupType',
QuestionID int 'http://QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')

EXEC sp_xml_removedocument @.docHandle

The reason you are only getting two rows in the resultset is because there are only two nodes that match your xpath expression /DivisionName/ClaimGroup/Questions/Answer/Ansoption.

What is the result set that you desire? I'm not certain what the results are you implying when you say you are expecting 4 rows.

Regards,

Galex

|||

Thanks for the reply first of all, luckily I have sorted it out now.

Thanks again

|||

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

|||Can you be more specific in your question? E.g. what element you want to access? After you have access, what do you want to do about that element?|||

This is the column in a table that contains the skill set of an individual candidate. on Passing the candidate id , i would access the access and the output i would give is like

coach=false, cdt=false,dit=false,fsa=false, lmr=false,iam=false,qefmemer=false,qefcertificatenumber=null,btec=false.

i would like to query for basic qualification and additional qualifications.

Thanks

Srini

|||

Anonymous wrote:

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

<xsl:for-each select="skill/coach/{@.type}>

</xsl:for-each>

sql

Open Xml

Hi All,

I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.

Definitely doing something wrong here, please advise!

DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'

DECLARE @.docHandle int

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc

SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) 'http://http://DVName',
DvcodeNo int 'http://http://DvcodeNo',
CustSurveyNo int 'http://../CustSurveyNo',
ClaimGroupType varchar (20) 'http://../ClaimGroupType',
QuestionID int 'http://QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')

EXEC sp_xml_removedocument @.docHandle

The reason you are only getting two rows in the resultset is because there are only two nodes that match your xpath expression /DivisionName/ClaimGroup/Questions/Answer/Ansoption.

What is the result set that you desire? I'm not certain what the results are you implying when you say you are expecting 4 rows.

Regards,

Galex

|||

Thanks for the reply first of all, luckily I have sorted it out now.

Thanks again

|||

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

|||Can you be more specific in your question? E.g. what element you want to access? After you have access, what do you want to do about that element?|||

This is the column in a table that contains the skill set of an individual candidate. on Passing the candidate id , i would access the access and the output i would give is like

coach=false, cdt=false,dit=false,fsa=false, lmr=false,iam=false,qefmemer=false,qefcertificatenumber=null,btec=false.

i would like to query for basic qualification and additional qualifications.

Thanks

Srini

|||

Anonymous wrote:

Hi

I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml

<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber /><level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>

Srini

<xsl:for-each select="skill/coach/{@.type}>

</xsl:for-each>

Open Query

Kudos to y'all!!! In the syntax...

SELECT * FROM OPENQUERY (linked server name, 'SQL Syntax')

...I'm having problems with the " ' " symbol. The SQL Syntax inside the OPENQUERY has a condition in the WHERE clause that has to be enclosed in apostophes. If it's a numeric value, I think that it doesn't need to be enclosed in apostrophes. But because the values are letters, it has to be enclosed in apostrophes. is there any way to work around this problem? I'm fetching data from an Oracle linked server by the way......I'm having problems with the " ' " symbol. ...

try enclosing the single quote in ' and '

like '''

hope this helps|||'select * from <table_name> where <column_name> = ''<string value>'' '|||try with 4 singlequotes on 2 sides,it works
pavan.