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 a variable

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @.CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @.CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @.CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@.CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.i don't think you can do that, putting a variable in the from clause

you'll have to use dynamic sql

so put that statement in a EXEC(.....)|||Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.|||sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.

if you are not using a variable for openquery, then just do this:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')|||Originally posted by Skippy_sc
Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.

Try this for instance:

DECLARE @.CQUERY varchar(8000)
SET @.CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
''SELECT top 10 * FROM master.dbo.sysobjects where name=''+'sysobjects'+'')'
select @.CQUERY
EXECUTE(@.CQUERY)|||Thank you very much fattyacid, it works fine now!

Skipsql

OPENQUERY end-of-file error

I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '

SELECT

*

FROM

(

WITH a AS

(

SELECT

a,

b,

c

FROM

table1

)

SELECT

x,

y,

z

FROM

a a1

INNER JOIN

table2 t2

ON a1.a = t2.a

)

')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.

I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.

Hope that helps,

John

|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?

|||

Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.

|||

Did this link offer you any help?

http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.

Thanks,

John

OPENQUERY end-of-file error

I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '

SELECT

*

FROM

(

WITH a AS

(

SELECT

a,

b,

c

FROM

table1

)

SELECT

x,

y,

z

FROM

a a1

INNER JOIN

table2 t2

ON a1.a = t2.a

)

')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.

I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.

Hope that helps,

John

|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?

|||

Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.

|||

Did this link offer you any help?

http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.

Thanks,

John

sql

OPENQUERY and string

Hi

Does anyone know how to include a string in the statement of an open query?

I want to execute the following query:

select * from TEST where A like 'A'

But if use this it in an openquery like it follows

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like 'A'')

The 'A' is not recognize like a string.

This is due to the single quote around 'A'

try this

''A'''

rule is if u need a quoted string put TWO quotes.

Gurpreet S. Gill

|||

You should escape quote by putting another quote.

So your query would be

' select * from TEST where A like ''A'' '

|||

Lot of lanugaues accepted the escape sequence char starts with \.

But in SQL Server (i remember in VB & MDX also) the same character will be repeated.

Code Snippet

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like ''A''')

sql

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:
> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
> > I have a connection string that has the clause "initial catalog=XXXXXX" in
> > it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> > opened and the login failed. Any ideas?
> >
> > Thank you.
> >
> > Keivn
> >
> >

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:

> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
[vbcol=seagreen]
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
>

Open connection in sql server

Try
Dim l_connString As String

l_connString = "Server=kangalert;database=Order;user id=sa;password=123;"
m_cn = New SqlConnection(l_connString)
m_cn.Open()

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
End Try

i got this in my mobile application, which try to open a connection directly to the sql server, but i got the following message

General Network error, check your network documentation.

Please go ahead and do general network troubleshooting to make sure you can connect (ping, port scan, etc.). That can be done with free vxUtils tool.

|||

i cannot connect directly to the sql server, but i can pull data from sql server.

It is really can connect directly to the sql server using mobile?

|||

If by “pull” you mean RDA/Replication, that is done via IIS. Direct connection is not going through IIS as it's just that - direct.

Yes, it is possible and just works assuming you have popper SQL Server and network setup.

If you not familiar with SQL Server connectivity configuration and/or network troubleshooting, you should seek help from your IT department.

|||

This is confusing - your code shows an attempt to use the System.Data.SqlClient and open a SqlConnection directly on the server using SQL Server authentication. You're reporting a network error when you try to establish the connection but then say that you can pull data from Sql Server. If you are running SqlCommands on the remote SqlServer and getting data back, you're in good shape. If you are trying to do RDA.Pull, your code is the wrong sort of connection. For RDA you need to be using System.Data.SqlServer.Ce and creating a SqlCeConnection.

Please clarify what you're trying to do and we'll try to help.

Darren

|||

yes, i agree with you. but my problem is the direct connection not the RDA . pull.

so do you have any idea of this error....

"General Network error, check your network documentation"

for you information i debug this simple application through a emulator. Should i use a real device to perfrom this testing?

|||You could install vxUtils to the emulator or device and perform general network diagnostics (ping, port scan, etc.) to see of server is reachable.

only one character added to database instread of full data string?

When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.

I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db??

SqlConnection SqlConnection1 =new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);SqlCommand SqlCommand1 =new SqlCommand("addRoute", SqlConnection1);SqlCommand1.CommandType = CommandType.StoredProcedure;SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@.ReturnValue", SqlDbType.Int);SqlParameter1.Direction = ParameterDirection.ReturnValue;SqlCommand1.Parameters.Add("@.xmlData", Request.Form["d"]);SqlConnection1.Open();SqlCommand1.ExecuteNonQuery();Response.Write(SqlCommand1.Parameters["@.ReturnValue"].Value);//Response.Write(Request.Form["d"]);

Here's my stored procedure too....

CREATE PROCEDURE [mydatabase].[addRoute]@.xmlDatavarcharASSET NOCOUNT ONINSERT INTO tbl_routes(xmldata)VALUES(@.xmlData)SELECT scope_identity()RETURN scope_identity()SET NOCOUNT OFF

|||

You need to set the size of the parameter. By default it takes only one character.

@.xmlDatavarchar(50)

Only enable the string truncation prevention of ANSI_WARNINGS

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

Cheers
Dave"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0401080924.40dd7da1@.posting.google.c om...
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?
> Cheers
> Dave

There's no 'subset' of ANSI_WARNINGS which will only raise an error on
string truncation, so your best bet is to fix your application, either by
making the stored proc parameter longer, or by validating the input in the
front end.

If neither of those are possible, then there aren't many options left,
except perhaps to raise an error if the parameter value is the same size as
the maximum possible size of the parameter data type. So if you have
char(20), assume that only values up to 19 characters are valid. But it
would be much better to fix the problem at the source, and validate the
input.

Simon|||David Sharp (dave@.daveandcaz.freeserve.co.uk) writes:
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?

To add to what Simon said, you would probably have any use for ANSI_WARNINGS
anyway. ANSI_WARNINGS produces an error if you try to assign a column
a value which is too long. However, variable and parameter assignment
still truncates silently, even with ANSI_WARNINGS ON.

I would however encourage you to switch to ANSI_WARNINGS for other reasons.
This setting is required is some contexts, more precisely in distributed
queries and when you used indexed views and indexed computed columns.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946B48FD1863Yazorman@.127.0.0.1>...
> I would however encourage you to switch to ANSI_WARNINGS for other reasons.
> This setting is required is some contexts, more precisely in distributed
> queries and when you used indexed views and indexed computed columns.

Thanks for your help. Sounds like there's no short cut to identify
when this happens. We'll have to physically go through and make sure
it is checked for in each sproc.

Cheers
Dave