EXEC @SQLString with Output Results

Hello, I have been working around this issue, but couldn't yet find any solution.
I have a stored procedure that calls a method to do a certain repetitive work.
In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.
for example, there is an input for a function called "Id"
Inside the function,
if Id = 111
I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.
Now, inside the function, I need to return a value by executing the above @.SQLString as follows:
EXEC @.SQLString
When I need is something like
EXEC @.SQLString, @.Total Output
Return (@.Total)
Are there any ideas ?
regardsProblem Solved
regardssql

Exchange Linked Server

I'm trying to run a view that calls a table in a linked server (linked to
Exchange on an SBS 03 box)
The linked server shows the correct tables in the Exchange public folders.
The query is;
CREATE VIEW dbo.vw_Exchange_Contacts
AS
SELECT CONVERT(nvarchar(50), [DAV:id]) AS DAVUID, CONVERT(nvarchar(50),
[urn:schemas:Customers:title]) AS Title, CONVERT(nvarchar(50),
[urn:schemas:Customers:sn]) AS Surname,
CONVERT(nvarchar(50), [urn:schemas:Customers:o]) AS Company,
CONVERT(nvarchar(50),
[urn:schemas:Customers:mailingstreet]) AS
Mailing_Street, CONVERT(nvarchar(25), [urn:schemas:Customers:mailingstate])
AS Mailing_State,
CONVERT(nvarchar(25),
[urn:schemas:Customers:mailingpostalcode]) AS Mailing_Postal_Code,
CONVERT(nvarchar(50),
[urn:schemas:Customers:mailingpostaladdress]) AS
Mailing_Postal_Address, CONVERT(nvarchar(50),
[urn:schemas:Customers:mailingcity])
AS Mailing_City, CONVERT(nvarchar(50),
[urn:schemas:Customers:fileas]) AS FileAS, CONVERT(nvarchar(50),
[urn:schemas:Customers:telephoneNumber]) AS Tel,
CONVERT(nvarchar(50), [urn:schemas:Customers:givenName]) AS FirstName
FROM OPENQUERY(Exchange,
'SELECT
"DAV:id","urn:schemas:Customers:title","urn:schema s:Customers:sn","urn:schemas:Customers:o","urn:sch emas:Customers:mailingstreet","urn:schemas:Custome rs:mailingstate","urn:schemas:Customers:mailingpos talcode","urn:schemas:Customers:mailingpostaladdre ss","
urn:schemas:Customers:mailingcity","urn:schemas:Cu stomers:fileas"
,"urn:schemas:Customers:telephoneNumber" ,"urn:schemas:Customers:givenName"
FROM "http://vj-sbs/public/Customers"')
Rowset_2
The problem is that the only field that is returned correctly is the DAV:ID
field; the rest are returned as NULL.
I've run the same sort of view on another SBS 2000 box correctly.
Any thoughts?
I should have added that if there is a better way to achieve the same thing,
i.e. bringing in the contacts from an exchange public folder then I'd also
love to hear about it.
Thanks in advance,
Mike
OnSite Applications.
"Mike" <yada@.yada.com> wrote in message news:42bf610c$1@.quokka.wn.com.au...
> I'm trying to run a view that calls a table in a linked server (linked to
> Exchange on an SBS 03 box)
> The linked server shows the correct tables in the Exchange public folders.
> The query is;
> CREATE VIEW dbo.vw_Exchange_Contacts
> AS
> SELECT CONVERT(nvarchar(50), [DAV:id]) AS DAVUID,
> CONVERT(nvarchar(50), [urn:schemas:Customers:title]) AS Title,
> CONVERT(nvarchar(50),
> [urn:schemas:Customers:sn]) AS Surname,
> CONVERT(nvarchar(50), [urn:schemas:Customers:o]) AS Company,
> CONVERT(nvarchar(50),
> [urn:schemas:Customers:mailingstreet]) AS
> Mailing_Street, CONVERT(nvarchar(25),
> [urn:schemas:Customers:mailingstate]) AS Mailing_State,
> CONVERT(nvarchar(25),
> [urn:schemas:Customers:mailingpostalcode]) AS Mailing_Postal_Code,
> CONVERT(nvarchar(50),
> [urn:schemas:Customers:mailingpostaladdress]) AS
> Mailing_Postal_Address, CONVERT(nvarchar(50),
> [urn:schemas:Customers:mailingcity])
> AS Mailing_City, CONVERT(nvarchar(50),
> [urn:schemas:Customers:fileas]) AS FileAS, CONVERT(nvarchar(50),
> [urn:schemas:Customers:telephoneNumber]) AS Tel,
> CONVERT(nvarchar(50), [urn:schemas:Customers:givenName]) AS FirstName
> FROM OPENQUERY(Exchange,
> 'SELECT
> "DAV:id","urn:schemas:Customers:title","urn:schema s:Customers:sn","urn:schemas:Customers:o","urn:sch emas:Customers:mailingstreet","urn:schemas:Custome rs:mailingstate","urn:schemas:Customers:mailingpos talcode","urn:schemas:Customers:mailingpostaladdre ss"
,"urn:schemas:Customers:mailingcity","urn:schemas: Customers:fileas"
> ,"urn:schemas:Customers:telephoneNumber"
> ,"urn:schemas:Customers:givenName" FROM "http://vj-sbs/public/Customers"')
> Rowset_2
> The problem is that the only field that is returned correctly is the
> DAV:ID field; the rest are returned as NULL.
> I've run the same sort of view on another SBS 2000 box correctly.
> Any thoughts?
>

Exchange Linked Server

I'm trying to run a view that calls a table in a linked server (linked to
Exchange on an SBS 03 box)
The linked server shows the correct tables in the Exchange public folders.
The query is;
CREATE VIEW dbo.vw_Exchange_Contacts
AS
SELECT CONVERT(nvarchar(50), [DAV:id]) AS DAVUID, CONVERT(nvarchar(5
0),
[urn:schemas:Customers:title]) AS Title, CONVERT(nvarchar(50),
[urn:schemas:Customers:sn]) AS Surname,
CONVERT(nvarchar(50), [urn:schemas:Customers:o]) AS Company,
CONVERT(nvarchar(50),
& #91;urn:schemas:Customers:mailingstreet]
) AS
Mailing_Street, CONVERT(nvarchar(25), [urn:schemas:Customers:mailingstat
e])
AS Mailing_State,
CONVERT(nvarchar(25),
& #91;urn:schemas:Customers:mailingpostalc
ode]) AS Mailing_Postal_Code,
CONVERT(nvarchar(50),
& #91;urn:schemas:Customers:mailingpostala
ddress]) AS
Mailing_Postal_Address, CONVERT(nvarchar(50),
[urn:schemas:Customers:mailingcity])
AS Mailing_City, CONVERT(nvarchar(50),
[urn:schemas:Customers:fileas]) AS FileAS, CONVERT(nvarchar(50),
& #91;urn:schemas:Customers:telephoneNumbe
r]) AS Tel,
CONVERT(nvarchar(50), [urn:schemas:Customers:givenName]) AS FirstName
FROM OPENQUERY(Exchange,
'SELECT
"DAV:id","urn:schemas:Customers:title","urn:schemas:Customers:sn","urn:schem
as:Customers:o","urn:schemas:Customers:mailingstreet","urn:schemas:Customers
:mailingstate","urn:schemas:Customers:mailingpostalcode","urn:schemas:Custom
ers:mailingpostaladdress","
urn:schemas:Customers:mailingcity","urn:schemas:Customers:fileas"
,"urn:schemas:Customers:telephoneNumber" ,"urn:schemas:Customers:givenName"
FROM "http://vj-sbs/public/Customers"')
Rowset_2
The problem is that the only field that is returned correctly is the DAV:ID
field; the rest are returned as NULL.
I've run the same sort of view on another SBS 2000 box correctly.
Any thoughts?I should have added that if there is a better way to achieve the same thing,
i.e. bringing in the contacts from an exchange public folder then I'd also
love to hear about it.
Thanks in advance,
Mike
OnSite Applications.
"Mike" <yada@.yada.com> wrote in message news:42bf610c$1@.quokka.wn.com.au...
> I'm trying to run a view that calls a table in a linked server (linked to
> Exchange on an SBS 03 box)
> The linked server shows the correct tables in the Exchange public folders.
> The query is;
> CREATE VIEW dbo.vw_Exchange_Contacts
> AS
> SELECT CONVERT(nvarchar(50), [DAV:id]) AS DAVUID,
> CONVERT(nvarchar(50), [urn:schemas:Customers:title]) AS Title,
> CONVERT(nvarchar(50),
> [urn:schemas:Customers:sn]) AS Surname,
> CONVERT(nvarchar(50), [urn:schemas:Customers:o]) AS Company,
> CONVERT(nvarchar(50),
> & #91;urn:schemas:Customers:mailingstreet]
) AS
> Mailing_Street, CONVERT(nvarchar(25),
> & #91;urn:schemas:Customers:mailingstate])
AS Mailing_State,
> CONVERT(nvarchar(25),
> & #91;urn:schemas:Customers:mailingpostalc
ode]) AS Mailing_Postal_Code,
> CONVERT(nvarchar(50),
> & #91;urn:schemas:Customers:mailingpostala
ddress]) AS
> Mailing_Postal_Address, CONVERT(nvarchar(50),
> [urn:schemas:Customers:mailingcity])
> AS Mailing_City, CONVERT(nvarchar(50),
> [urn:schemas:Customers:fileas]) AS FileAS, CONVERT(nvarchar(50),
> & #91;urn:schemas:Customers:telephoneNumbe
r]) AS Tel,
> CONVERT(nvarchar(50), [urn:schemas:Customers:givenName]) AS FirstName
> FROM OPENQUERY(Exchange,
> 'SELECT
> "DAV:id","urn:schemas:Customers:title","urn:schemas:Customers:sn","urn:schemas:Cus
tomers:o","urn:schemas:Customers:mailingstreet","urn:schemas:Customers:mailingstate"
,"urn:schemas:Customers:mailingpostalcode"," urn:schemas:Customers:mailingpostaladdre
ss"
,"urn:schemas:Customers:mailingcity","urn:schemas:Customers:fileas"
> ,"urn:schemas:Customers:telephoneNumber"
> ,"urn:schemas:Customers:givenName" FROM "http://vj-sbs/public/Customers"')
> Rowset_2
> The problem is that the only field that is returned correctly is the
> DAV:ID field; the rest are returned as NULL.
> I've run the same sort of view on another SBS 2000 box correctly.
> Any thoughts?
>

Excel report migration + html display

Hi,
We have the following 2 problem areas in our application:
1) We have a excel report that calls a SQL SP which returns 13 resultsets.
These resultsets are then read by a excel macro and a report is printed in
the worksheet. We now want to move this to SSRS 2000. I was trying to find a
easy way out by embedding a excel object in the report and use the same
macro. Is this possible? If not then can anyone suggest any workaround? For
the SP's returning multiple resultsets, we are thinking of creating 13
datasets each of them will have their own SP.
In order to convert to SSRS 2000, do i need to recode the entire logic of
the macro in SSRS? or is there any better way.
2) As displaying of RTF is not supported in SSRS 2000, I was thinking of
writing some code which will parse the RTF and spit out a formatted HTML. Is
it possible to display HTML in the report? If not, then is there any
workaround?
3) I read in some of the posts that supporting HTML in SSRS will be a
security hole-what does that mean?
Thanks in advance
--
Afaq> <snip>
> We now want to move this to SSRS 2000. I was trying to
> find a
> easy way out by embedding a excel object in the report and use the
> same
> macro. Is this possible? If not then can anyone suggest any
> workaround? For
> the SP's returning multiple resultsets, we are thinking of creating 13
> datasets each of them will have their own SP.
> In order to convert to SSRS 2000, do i need to recode the entire logic
> of
> the macro in SSRS? or is there any better way.
> <snip>
> Thanks in advance
Hello Afaq,
SoftArtisans OfficeWriter is a report designer and custom rendering extension
for SSRS that lets you use real Excel and Word files as report templates,
and deliver those files intact. If you put VBA code in the template, it
will be preserved when it's delievered by Reporting Services:
http://officewriter.softartisans.com/officewriter-250.aspx
Best,
Chris