OPENQUERY vs EXECUTE on a linked server -- what is better?

Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server 2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't been able to find a comparison between the two.

Have there been any tests of the difference?

What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?

To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.

The new option with EXECUTE would be something like the example in MSDN (Example J.) at:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO

Well, openquery/rowset/datasource only takes literal string (i.e. you cannot user string variable). The new Execute syntax allows you do the same pass-through as with openquery but also allows the use of variable. If you're doing lots of cross server invocation, this is certainly a major benefit.

As for perf implication, there wouldn't be much of a difference between both methods. I would bet the Exec would actually be better.

sql

OPENQUERY and unicode from MySQL

Hi, all. Using SQL 2005. When I execute this query against a MySQL database set up as a linked server, cyrillic text stored in unicode in MySQL shows up as question marks (?).

SELECT *
FROM
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM documents
)

Clearly, some kind of unicode issue. I've spent a few hours reading in BOL and searching online. Must be something simple, but I'm not finding a solution. If anyone has suggestions, I'd be very grateful.

Stephen

Just to follow up, my testing this morning suggests that this is an issue with the MySQL OBDC driver, used to create the connection to the linked server in SQL Server. So if anyone has solved this issue before, of course I'd be happy to hear the answer. Has anyone used a different driver to connect to MySQL as a linked server? I don't think I can use the Cherry City Software OLE DB provider, because it doesn't seem to support large text columns (http://cherrycitysoftware.com/CCS/Providers/ProvMySQL.aspx). I need a way to connect to MySQL as a linked server, using unicode (multilingual text in different rows) and text storage in some row columns at 50,000 bytes or more.

Thanks for any suggestions.

Stephen

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

openquery - sp_addlinkedserver problem

Hello, i'm trying to figure out how to solve my challenge.

my goal is to access the analysis service to execute an mdx-query.

the resultset is placed in a temp-table in my 'normal' database.

when i login to my db as integrated user (Administrator in this case) everything works tiptop.

but since we cannot use (for now) i have to use a sql-login.

when i login as that, i have trouble getting the resultset.

atm my code looks like this:

USE master

GO

/* Add new linked server */

EXEC sp_addlinkedserver

@.server='SASDEL003_SSAS', -- local SQL name given to the linked server

@.srvproduct='', -- not used

@.provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)

@.datasrc='SASDEL003', -- analysis server name (machine name)

@.catalog='TNT Grip AnalysisServices' -- default catalog/database

GO

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'SASDEL003_SSAS'

, @.useself = false

, @.locallogin = 'domain\foo'

--, @.rmtuser = 'foo'

--, @.rmtpassword = 'bar'

GO

SELECT *

FROM OPENQUERY(SASDEL003_SSAS,'SELECT some highly interesting query')

GO

/* Remove any previous references to the linked server */

EXEC sp_droplinkedsrvlogin @.rmtsrvname= 'SASDEL003_SSAS' , @.locallogin= 'SASDEL003\foo'

GO

EXEC sp_dropserver 'SASDEL003_SSAS'

GO

but i get the error:

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS".what i'm seeing in the profiler i'm logging in the ssas-database with "anonymous user"

i don't understand that, because i use linked server with sp_addlinkedsrvlogin

should i allow anonymous login then?

Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned b
y
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:

> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>sql

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned by
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:
> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>

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)