Hide Pagefooter

Hi All,
I would like to show pagefooter on first page only. I tried with
Visibility -> Hidden = IIF(Globals!PageNumber=1,False,True)
It succesfully hide but showing space on other pages.
Please suggest any solution.
Kind Regards
ZahidWhen the report is rendered at run-time it will always display the
space allocated for the page footer, even if you set the Hidden
property to True.
zahid76@.gmail.com wrote:
> Hi All,
> I would like to show pagefooter on first page only. I tried with
> Visibility -> Hidden = IIF(Globals!PageNumber=1,False,True)
> It succesfully hide but showing space on other pages.
> Please suggest any solution.
> Kind Regards
> Zahid

hide a textbox

hi,
I want to hide a texbox using an expresion like this:
=IIF(Parameters!rs:Format=PDF,true,false)
but doesn't work, why'
--
Sorry for my English :)That would be great if it worked!! Unfortunately, the rs: and rc:
parameters are not exposed as part of the parameters collection. Even this
fails with #Error:
=Parameters("rs:Format").Value
Does anyone else know how we can get to this info?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
> hi,
> I want to hide a texbox using an expresion like this:
> =IIF(Parameters!rs:Format=PDF,true,false)
> but doesn't work, why'
> --
> Sorry for my English :)|||Just pass in a separate parameter, if those are not exposed. It maight be
ugly but it would work.
"Jeff A. Stucker" wrote:
> That would be great if it worked!! Unfortunately, the rs: and rc:
> parameters are not exposed as part of the parameters collection. Even this
> fails with #Error:
> =Parameters("rs:Format").Value
> Does anyone else know how we can get to this info?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
> > hi,
> >
> > I want to hide a texbox using an expresion like this:
> >
> > =IIF(Parameters!rs:Format=PDF,true,false)
> >
> > but doesn't work, why'
> > --
> > Sorry for my English :)
>
>|||Yes, that is possible, but it's not the point. What would be nice if we
could programmatically determine which format the user selected for
rendering.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
> Just pass in a separate parameter, if those are not exposed. It maight be
> ugly but it would work.
> "Jeff A. Stucker" wrote:
>> That would be great if it worked!! Unfortunately, the rs: and rc:
>> parameters are not exposed as part of the parameters collection. Even
>> this
>> fails with #Error:
>> =Parameters("rs:Format").Value
>> Does anyone else know how we can get to this info?
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
>> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
>> > hi,
>> >
>> > I want to hide a texbox using an expresion like this:
>> >
>> > =IIF(Parameters!rs:Format=PDF,true,false)
>> >
>> > but doesn't work, why'
>> > --
>> > Sorry for my English :)
>>|||You may be interested in this newsgroup posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=05ea108a-f79c-46e6-813c-de23b24d0619&sloc=en-us
Similar things apply here. Expressions get evaluated at report processing
time and the results may be stored explicitly as execution snapshots or
cached by the report server (e.g. as user session snapshots). These
snapshots are a representation of a processed report which is independent of
any rendering output format. Along the same lines, the RenderingObjectModel
exposed by the RS processing engine is independent of any output format.
Rendering extensions only access public properties exposed in the
RenderingObjectModel.
When someone accesses a report (e.g. through URL-access or through report
manager), the report does not necessarily get re-processed, but instead is
just rendered from the already processed snapshot. Hence, expressions in the
report don't get reevaluated (except for those in page header/footers).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:expJU1s5EHA.1260@.TK2MSFTNGP12.phx.gbl...
> Yes, that is possible, but it's not the point. What would be nice if we
> could programmatically determine which format the user selected for
> rendering.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
> news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
> > Just pass in a separate parameter, if those are not exposed. It maight
be
> > ugly but it would work.
> >
> > "Jeff A. Stucker" wrote:
> >
> >> That would be great if it worked!! Unfortunately, the rs: and rc:
> >> parameters are not exposed as part of the parameters collection. Even
> >> this
> >> fails with #Error:
> >>
> >> =Parameters("rs:Format").Value
> >>
> >> Does anyone else know how we can get to this info?
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
> >> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
> >> > hi,
> >> >
> >> > I want to hide a texbox using an expresion like this:
> >> >
> >> > =IIF(Parameters!rs:Format=PDF,true,false)
> >> >
> >> > but doesn't work, why'
> >> > --
> >> > Sorry for my English :)
> >>
> >>
> >>
>|||Thanks for the explanation, Robert, that helps a lot with understanding the
issues.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23CQIxev5EHA.2568@.TK2MSFTNGP11.phx.gbl...
> You may be interested in this newsgroup posting:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=05ea108a-f79c-46e6-813c-de23b24d0619&sloc=en-us
> Similar things apply here. Expressions get evaluated at report processing
> time and the results may be stored explicitly as execution snapshots or
> cached by the report server (e.g. as user session snapshots). These
> snapshots are a representation of a processed report which is independent
> of
> any rendering output format. Along the same lines, the
> RenderingObjectModel
> exposed by the RS processing engine is independent of any output format.
> Rendering extensions only access public properties exposed in the
> RenderingObjectModel.
> When someone accesses a report (e.g. through URL-access or through report
> manager), the report does not necessarily get re-processed, but instead is
> just rendered from the already processed snapshot. Hence, expressions in
> the
> report don't get reevaluated (except for those in page header/footers).
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:expJU1s5EHA.1260@.TK2MSFTNGP12.phx.gbl...
>> Yes, that is possible, but it's not the point. What would be nice if we
>> could programmatically determine which format the user selected for
>> rendering.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
>> news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
>> > Just pass in a separate parameter, if those are not exposed. It maight
> be
>> > ugly but it would work.
>> >
>> > "Jeff A. Stucker" wrote:
>> >
>> >> That would be great if it worked!! Unfortunately, the rs: and rc:
>> >> parameters are not exposed as part of the parameters collection. Even
>> >> this
>> >> fails with #Error:
>> >>
>> >> =Parameters("rs:Format").Value
>> >>
>> >> Does anyone else know how we can get to this info?
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
>> >> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
>> >> > hi,
>> >> >
>> >> > I want to hide a texbox using an expresion like this:
>> >> >
>> >> > =IIF(Parameters!rs:Format=PDF,true,false)
>> >> >
>> >> > but doesn't work, why'
>> >> > --
>> >> > Sorry for my English :)
>> >>
>> >>
>> >>
>>
>|||Robert,
Okay, now that we understand this as a limitation, is there an intermediate
solution that could make use of the format without changing the underlying
snapshot, for example, like show/hide selections do. The biggest use of
knowing the rendering format would be simply to show or hide elements, such
as footers, disclaimers, printer-friendly options, logos, etc.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23CQIxev5EHA.2568@.TK2MSFTNGP11.phx.gbl...
> You may be interested in this newsgroup posting:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=05ea108a-f79c-46e6-813c-de23b24d0619&sloc=en-us
> Similar things apply here. Expressions get evaluated at report processing
> time and the results may be stored explicitly as execution snapshots or
> cached by the report server (e.g. as user session snapshots). These
> snapshots are a representation of a processed report which is independent
> of
> any rendering output format. Along the same lines, the
> RenderingObjectModel
> exposed by the RS processing engine is independent of any output format.
> Rendering extensions only access public properties exposed in the
> RenderingObjectModel.
> When someone accesses a report (e.g. through URL-access or through report
> manager), the report does not necessarily get re-processed, but instead is
> just rendered from the already processed snapshot. Hence, expressions in
> the
> report don't get reevaluated (except for those in page header/footers).
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:expJU1s5EHA.1260@.TK2MSFTNGP12.phx.gbl...
>> Yes, that is possible, but it's not the point. What would be nice if we
>> could programmatically determine which format the user selected for
>> rendering.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
>> news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
>> > Just pass in a separate parameter, if those are not exposed. It maight
> be
>> > ugly but it would work.
>> >
>> > "Jeff A. Stucker" wrote:
>> >
>> >> That would be great if it worked!! Unfortunately, the rs: and rc:
>> >> parameters are not exposed as part of the parameters collection. Even
>> >> this
>> >> fails with #Error:
>> >>
>> >> =Parameters("rs:Format").Value
>> >>
>> >> Does anyone else know how we can get to this info?
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
>> >> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
>> >> > hi,
>> >> >
>> >> > I want to hide a texbox using an expresion like this:
>> >> >
>> >> > =IIF(Parameters!rs:Format=PDF,true,false)
>> >> >
>> >> > but doesn't work, why'
>> >> > --
>> >> > Sorry for my English :)
>> >>
>> >>
>> >>
>>
>|||Jeff, I would think the architecture of output-format independent report
representations is an advantage in many cases rather than a limitation. E.g.
the output-format independent RenderingObjectModel (ROM) allows anyone to
implement their own custom rendering extensions without the need to plug
into the RS processing engine. Also, if you plug in a custom processing
control in the upcoming RS2005 release, it only needs to generate
output-format independent ROM objects and does not need to worry about any
known (or unknown) output formats and rendering extensions.
Anyway, we understand that the approach has limitations and we will look
into addressing these in future releases. For now, I think you have
basically these options:
* use a report parameter (which could be hidden from the user) to make
decisions at report processing time. Note: caching takes parameter values
into consideration.
* create variations of the report optimized for certain usages, e.g. one
specifically designed for Excel output, another specifically designed for
archiving purposes.
* adding so-called custom elements (in RS 2000) or custom properties (in RS
2005) to the RDL. They represent metadata and will just pass through the
processing engine and get exposed in the RenderingObjectModel. You could
then write your own custom rendering extension (this is hard) which
interprets the metadata and makes decisions at rendering time.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%235TXa9E6EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Robert,
> Okay, now that we understand this as a limitation, is there an
intermediate
> solution that could make use of the format without changing the underlying
> snapshot, for example, like show/hide selections do. The biggest use of
> knowing the rendering format would be simply to show or hide elements,
such
> as footers, disclaimers, printer-friendly options, logos, etc.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23CQIxev5EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > You may be interested in this newsgroup posting:
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=05ea108a-f79c-46e6-813c-de23b24d0619&sloc=en-us
> >
> > Similar things apply here. Expressions get evaluated at report
processing
> > time and the results may be stored explicitly as execution snapshots or
> > cached by the report server (e.g. as user session snapshots). These
> > snapshots are a representation of a processed report which is
independent
> > of
> > any rendering output format. Along the same lines, the
> > RenderingObjectModel
> > exposed by the RS processing engine is independent of any output format.
> > Rendering extensions only access public properties exposed in the
> > RenderingObjectModel.
> > When someone accesses a report (e.g. through URL-access or through
report
> > manager), the report does not necessarily get re-processed, but instead
is
> > just rendered from the already processed snapshot. Hence, expressions in
> > the
> > report don't get reevaluated (except for those in page header/footers).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:expJU1s5EHA.1260@.TK2MSFTNGP12.phx.gbl...
> >> Yes, that is possible, but it's not the point. What would be nice if
we
> >> could programmatically determine which format the user selected for
> >> rendering.
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
> >> news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
> >> > Just pass in a separate parameter, if those are not exposed. It
maight
> > be
> >> > ugly but it would work.
> >> >
> >> > "Jeff A. Stucker" wrote:
> >> >
> >> >> That would be great if it worked!! Unfortunately, the rs: and rc:
> >> >> parameters are not exposed as part of the parameters collection.
Even
> >> >> this
> >> >> fails with #Error:
> >> >>
> >> >> =Parameters("rs:Format").Value
> >> >>
> >> >> Does anyone else know how we can get to this info?
> >> >> --
> >> >> Cheers,
> >> >>
> >> >> '(' Jeff A. Stucker
> >> >> \
> >> >>
> >> >> Business Intelligence
> >> >> www.criadvantage.com
> >> >> ---
> >> >> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
> >> >> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
> >> >> > hi,
> >> >> >
> >> >> > I want to hide a texbox using an expresion like this:
> >> >> >
> >> >> > =IIF(Parameters!rs:Format=PDF,true,false)
> >> >> >
> >> >> > but doesn't work, why'
> >> >> > --
> >> >> > Sorry for my English :)
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||Again, I appreciate the explanation. It is nice having
output-format-independent intermediate data, for many reasons.
One thing to consider is that -- especially until new and better rendering
extensions emerge -- we have to work around the limitations of each
extension, and even the inherent incompatibilities between extensions. For
my part, creating different versions of a report for different extensions is
no huge deal. (At least for now.) In the future, users could tell me
otherwise. You never know what users are going to say. ;-)
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ORImZYH6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Jeff, I would think the architecture of output-format independent report
> representations is an advantage in many cases rather than a limitation.
> E.g.
> the output-format independent RenderingObjectModel (ROM) allows anyone to
> implement their own custom rendering extensions without the need to plug
> into the RS processing engine. Also, if you plug in a custom processing
> control in the upcoming RS2005 release, it only needs to generate
> output-format independent ROM objects and does not need to worry about any
> known (or unknown) output formats and rendering extensions.
> Anyway, we understand that the approach has limitations and we will look
> into addressing these in future releases. For now, I think you have
> basically these options:
> * use a report parameter (which could be hidden from the user) to make
> decisions at report processing time. Note: caching takes parameter values
> into consideration.
> * create variations of the report optimized for certain usages, e.g. one
> specifically designed for Excel output, another specifically designed for
> archiving purposes.
> * adding so-called custom elements (in RS 2000) or custom properties (in
> RS
> 2005) to the RDL. They represent metadata and will just pass through the
> processing engine and get exposed in the RenderingObjectModel. You could
> then write your own custom rendering extension (this is hard) which
> interprets the metadata and makes decisions at rendering time.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%235TXa9E6EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Robert,
>> Okay, now that we understand this as a limitation, is there an
> intermediate
>> solution that could make use of the format without changing the
>> underlying
>> snapshot, for example, like show/hide selections do. The biggest use of
>> knowing the rendering format would be simply to show or hide elements,
> such
>> as footers, disclaimers, printer-friendly options, logos, etc.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23CQIxev5EHA.2568@.TK2MSFTNGP11.phx.gbl...
>> > You may be interested in this newsgroup posting:
>> >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=05ea108a-f79c-46e6-813c-de23b24d0619&sloc=en-us
>> >
>> > Similar things apply here. Expressions get evaluated at report
> processing
>> > time and the results may be stored explicitly as execution snapshots or
>> > cached by the report server (e.g. as user session snapshots). These
>> > snapshots are a representation of a processed report which is
> independent
>> > of
>> > any rendering output format. Along the same lines, the
>> > RenderingObjectModel
>> > exposed by the RS processing engine is independent of any output
>> > format.
>> > Rendering extensions only access public properties exposed in the
>> > RenderingObjectModel.
>> > When someone accesses a report (e.g. through URL-access or through
> report
>> > manager), the report does not necessarily get re-processed, but instead
> is
>> > just rendered from the already processed snapshot. Hence, expressions
>> > in
>> > the
>> > report don't get reevaluated (except for those in page header/footers).
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> >
>> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> > news:expJU1s5EHA.1260@.TK2MSFTNGP12.phx.gbl...
>> >> Yes, that is possible, but it's not the point. What would be nice if
> we
>> >> could programmatically determine which format the user selected for
>> >> rendering.
>> >>
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "Pinolian" <Pinolian@.discussions.microsoft.com> wrote in message
>> >> news:A5927A07-0F08-46A6-88FB-A186AE655E0B@.microsoft.com...
>> >> > Just pass in a separate parameter, if those are not exposed. It
> maight
>> > be
>> >> > ugly but it would work.
>> >> >
>> >> > "Jeff A. Stucker" wrote:
>> >> >
>> >> >> That would be great if it worked!! Unfortunately, the rs: and rc:
>> >> >> parameters are not exposed as part of the parameters collection.
> Even
>> >> >> this
>> >> >> fails with #Error:
>> >> >>
>> >> >> =Parameters("rs:Format").Value
>> >> >>
>> >> >> Does anyone else know how we can get to this info?
>> >> >> --
>> >> >> Cheers,
>> >> >>
>> >> >> '(' Jeff A. Stucker
>> >> >> \
>> >> >>
>> >> >> Business Intelligence
>> >> >> www.criadvantage.com
>> >> >> ---
>> >> >> "Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
>> >> >> news:7D8ED494-A342-4DF9-8993-42E6579483B1@.microsoft.com...
>> >> >> > hi,
>> >> >> >
>> >> >> > I want to hide a texbox using an expresion like this:
>> >> >> >
>> >> >> > =IIF(Parameters!rs:Format=PDF,true,false)
>> >> >> >
>> >> >> > but doesn't work, why'
>> >> >> > --
>> >> >> > Sorry for my English :)
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>sql

Hidden columns showing on export

I am allowing users to specify through a parameter whether they want a column
to be visible or not. If they specify visible = false, the column does not
show on the report. However, when that same report is exported, the column is
visible in the export.
Does anyone have a solution for this?Hello.
I am having a similar problem. I'm trying to make some table columns hidden
(collapsed). The regular online report view is fine and the hidden columns
are hidden and propery collapsed. However, when I export (to pdf) the hidden
columns are blank but not collapsed, except for the table footer row, so the
table footer is misaligned with the rest of the table. The columns in the
table footer row collapsed propery, but the rest of the table's hidden
columns are blank but did not collapse. So I end up with a table with more
header and detail columns than footer columns.
Any idea what is happening?
Thanks,
Brian.
"khonerka" wrote:
> I am allowing users to specify through a parameter whether they want a column
> to be visible or not. If they specify visible = false, the column does not
> show on the report. However, when that same report is exported, the column is
> visible in the export.
> Does anyone have a solution for this?

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue