forcing remote evaluation of like filters with linked servers

i have server1
server 2 is linked to server1
server 3 is linked to server1
all sql2005
on server1 i go
select foo from [server2].[db].[dbo].table where bar like '%ff%'
union all
select foo from [server3].[db].[dbo].table where bar like '%ff%'
by looking at the execution plan I can see that the remote selects have no
filter on them and that the filtering is applied locally by server1. this is
of course extremely inefficient.
The docs say that if I say thet the provider has sql like capability then
the query planner 'might' consider asking the remote server to do the like
processing.
I did that (even though it seems redundant since server1 knows that this is
another sql sevrer database).
What other magic buttons do I have to pressHi Paul
Using OPENQUERY would allow you to filter remotely.
John
"paul moore" wrote:
> i have server1
> server 2 is linked to server1
> server 3 is linked to server1
> all sql2005
> on server1 i go
> select foo from [server2].[db].[dbo].table where bar like '%ff%'
> union all
> select foo from [server3].[db].[dbo].table where bar like '%ff%'
> by looking at the execution plan I can see that the remote selects have no
> filter on them and that the filtering is applied locally by server1. this is
> of course extremely inefficient.
> The docs say that if I say thet the provider has sql like capability then
> the query planner 'might' consider asking the remote server to do the like
> processing.
> I did that (even though it seems redundant since server1 knows that this is
> another sql sevrer database).
> What other magic buttons do I have to press
>
>|||thanks - works great
seems like the 'normal' query processor should do it automatically though -
defaulting to the most inefficient way is surprising
"John Bell" wrote:
> Hi Paul
> Using OPENQUERY would allow you to filter remotely.
> John
> "paul moore" wrote:
> > i have server1
> > server 2 is linked to server1
> > server 3 is linked to server1
> > all sql2005
> >
> > on server1 i go
> > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > union all
> > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> >
> > by looking at the execution plan I can see that the remote selects have no
> > filter on them and that the filtering is applied locally by server1. this is
> > of course extremely inefficient.
> >
> > The docs say that if I say thet the provider has sql like capability then
> > the query planner 'might' consider asking the remote server to do the like
> > processing.
> > I did that (even though it seems redundant since server1 knows that this is
> > another sql sevrer database).
> >
> > What other magic buttons do I have to press
> >
> >
> >|||Hi
I think if you were not using like you would be ok, but I haven't tested it!
John
"paul moore" wrote:
> thanks - works great
> seems like the 'normal' query processor should do it automatically though -
> defaulting to the most inefficient way is surprising
> "John Bell" wrote:
> > Hi Paul
> >
> > Using OPENQUERY would allow you to filter remotely.
> >
> > John
> >
> > "paul moore" wrote:
> >
> > > i have server1
> > > server 2 is linked to server1
> > > server 3 is linked to server1
> > > all sql2005
> > >
> > > on server1 i go
> > > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > > union all
> > > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> > >
> > > by looking at the execution plan I can see that the remote selects have no
> > > filter on them and that the filtering is applied locally by server1. this is
> > > of course extremely inefficient.
> > >
> > > The docs say that if I say thet the provider has sql like capability then
> > > the query planner 'might' consider asking the remote server to do the like
> > > processing.
> > > I did that (even though it seems redundant since server1 knows that this is
> > > another sql sevrer database).
> > >
> > > What other magic buttons do I have to press
> > >
> > >
> > >

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Force Encryption

I need to setup SQL encryption on a remote server running W2k3, SQL2k5 std.
The server doesn't have access to a CA. SQL connections are limited to my
server and IIS running on the same remote server.
Can I use a SelfSSL certificate created with the IIS 6.0 Toolkit when
configuring forcing encryption at the network level? If so, are there any
recommendations or pitfalls?
ThanksWhile I've never had to actually do this, yeah, it should be possible. Just
be careful that you setup the domain correctly in the cert and theoretically
you should be fine.
"BigSam" wrote:

> I need to setup SQL encryption on a remote server running W2k3, SQL2k5 std
.
> The server doesn't have access to a CA. SQL connections are limited to my
> server and IIS running on the same remote server.
> Can I use a SelfSSL certificate created with the IIS 6.0 Toolkit when
> configuring forcing encryption at the network level? If so, are there any
> recommendations or pitfalls?
> Thanks

FOR XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
Michael
FOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>
|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the[vbcol=seagreen]
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>
|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...[vbcol=seagreen]
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

FOR XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
MichaelFOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

For Remus - Access to the remote server is denied because the current security context is not tr

Remus (or anyone who can help) -

I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

Do you still have this problem? I was in vacantion and could not respond to forum questions.

You have to set the local database as trustworthy and the dbo of the local database must correspond to a login that has the 'authenticate server' priviledge.

HTH,
~ Remus

For Remus - Access to the remote server is denied because the current security context is not tr

Remus (or anyone who can help) -

I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

Do you still have this problem? I was in vacantion and could not respond to forum questions.

You have to set the local database as trustworthy and the dbo of the local database must correspond to a login that has the 'authenticate server' priviledge.

HTH,
~ Remus