Alternate to sql web assistant

I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users.

these html files that are generated are processed via a JOB in sql server.

I am in process of replacing of the web assistant procedures.

Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure.

Is there any other possible way to do it?

Hi Brinda,

I have similar stored procedures that use web assistnant to generate xls files and then email them. I have heard that web assistant can be replaced with reporting services in SQL2K5, but I am unaware of the details of how to do this-if anyone has details on this I am interested to learn about it.

Thanks,

-Dave

ALTER TABLE SQL7

I used this command on SQL2K to change the length of a column from 150 to
200
ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
I've tried all vesions and perversions of it on SQL7 but keep gettign the
error that there is something wrong around COLUMN
David A. BeckDavid,
Please include the text of the message that you are getting.
Russell Fields
"David A. Beck" <davidb@.beckb.com> wrote in message
news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> I used this command on SQL2K to change the length of a column from 150 to
> 200
> ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> I've tried all vesions and perversions of it on SQL7 but keep gettign the
> error that there is something wrong around COLUMN
> David A. Beck
>|||Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'COLUMN'.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> David,
> Please include the text of the message that you are getting.
> Russell Fields
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > I used this command on SQL2K to change the length of a column from 150
to
> > 200
> > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > I've tried all vesions and perversions of it on SQL7 but keep gettign
the
> > error that there is something wrong around COLUMN
> >
> > David A. Beck
> >
> >
>|||Is the database set to 6.5 compability?
"David A. Beck" <davidb@.beckb.com> wrote in message
news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'COLUMN'.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> > David,
> >
> > Please include the text of the message that you are getting.
> >
> > Russell Fields
> > "David A. Beck" <davidb@.beckb.com> wrote in message
> > news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > > I used this command on SQL2K to change the length of a column from 150
> to
> > > 200
> > > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > > I've tried all vesions and perversions of it on SQL7 but keep gettign
> the
> > > error that there is something wrong around COLUMN
> > >
> > > David A. Beck
> > >
> > >
> >
> >
>|||Yes it is.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uUJU%23sc6DHA.2908@.tk2msftngp13.phx.gbl...
> Is the database set to 6.5 compability?
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'COLUMN'.
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> > > David,
> > >
> > > Please include the text of the message that you are getting.
> > >
> > > Russell Fields
> > > "David A. Beck" <davidb@.beckb.com> wrote in message
> > > news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > > > I used this command on SQL2K to change the length of a column from
150
> > to
> > > > 200
> > > > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > > > I've tried all vesions and perversions of it on SQL7 but keep
gettign
> > the
> > > > error that there is something wrong around COLUMN
> > > >
> > > > David A. Beck
> > > >
> > > >
> > >
> > >
> >
> >
>|||That's the problem. ALTER support was introduced with v7.
"David A. Beck" <davidb@.beckb.com> wrote in message
news:umAJ7yc6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Yes it is.
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:uUJU%23sc6DHA.2908@.tk2msftngp13.phx.gbl...
> > Is the database set to 6.5 compability?
> >
> > "David A. Beck" <davidb@.beckb.com> wrote in message
> > news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near 'COLUMN'.
> > >
> > >
> > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> > > > David,
> > > >
> > > > Please include the text of the message that you are getting.
> > > >
> > > > Russell Fields
> > > > "David A. Beck" <davidb@.beckb.com> wrote in message
> > > > news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > > > > I used this command on SQL2K to change the length of a column from
> 150
> > > to
> > > > > 200
> > > > > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > > > > I've tried all vesions and perversions of it on SQL7 but keep
> gettign
> > > the
> > > > > error that there is something wrong around COLUMN
> > > > >
> > > > > David A. Beck
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Scott, thanks. The light went on when I read your question. I did change it
and it works. Thanks so much.
David A. Beck
"Scott Morris" <bogus@.bogus.com> wrote in message
news:epMcOFd6DHA.1752@.tk2msftngp13.phx.gbl...
> That's the problem. ALTER support was introduced with v7.
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:umAJ7yc6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Yes it is.
> >
> > "Scott Morris" <bogus@.bogus.com> wrote in message
> > news:uUJU%23sc6DHA.2908@.tk2msftngp13.phx.gbl...
> > > Is the database set to 6.5 compability?
> > >
> > > "David A. Beck" <davidb@.beckb.com> wrote in message
> > > news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > > > Server: Msg 170, Level 15, State 1, Line 1
> > > > Line 1: Incorrect syntax near 'COLUMN'.
> > > >
> > > >
> > > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > > news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> > > > > David,
> > > > >
> > > > > Please include the text of the message that you are getting.
> > > > >
> > > > > Russell Fields
> > > > > "David A. Beck" <davidb@.beckb.com> wrote in message
> > > > > news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > > > > > I used this command on SQL2K to change the length of a column
from
> > 150
> > > > to
> > > > > > 200
> > > > > > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > > > > > I've tried all vesions and perversions of it on SQL7 but keep
> > gettign
> > > > the
> > > > > > error that there is something wrong around COLUMN
> > > > > >
> > > > > > David A. Beck
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||what errors do you get?
"David A. Beck" <davidb@.beckb.com> wrote in message
news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> I used this command on SQL2K to change the length of a column from 150 to
> 200
> ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> I've tried all vesions and perversions of it on SQL7 but keep gettign the
> error that there is something wrong around COLUMN
> David A. Beck
>|||Richard:
Please read further up the string. I already answered that. The problem was
that the database was set to 6.5 compatability. Thanks.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uzq1qmm6DHA.2720@.TK2MSFTNGP09.phx.gbl...
> what errors do you get?
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
> > I used this command on SQL2K to change the length of a column from 150
to
> > 200
> > ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> > I've tried all vesions and perversions of it on SQL7 but keep gettign
the
> > error that there is something wrong around COLUMN
> >
> > David A. Beck
> >
> >
>

ALTER TABLE SQL7

I used this command on SQL2K to change the length of a column from 150 to
200
ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
I've tried all vesions and perversions of it on SQL7 but keep gettign the
error that there is something wrong around COLUMN
David A. BeckDavid,
Please include the text of the message that you are getting.
Russell Fields
"David A. Beck" <davidb@.beckb.com> wrote in message
news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I used this command on SQL2K to change the length of a column from 150 to
> 200
> ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> I've tried all vesions and perversions of it on SQL7 but keep gettign the
> error that there is something wrong around COLUMN
> David A. Beck
>

---

|||Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'COLUMN'.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...

quote:

---

> David,
> Please include the text of the message that you are getting.
> Russell Fields
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
to[QUOTE]
the[QUOTE]
>

---

|||Is the database set to 6.5 compability?
"David A. Beck" <davidb@.beckb.com> wrote in message
news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'COLUMN'.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OwHAJic6DHA.696@.tk2msftngp13.phx.gbl...
> to
> the
>

---

|||Yes it is.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uUJU%23sc6DHA.2908@.tk2msftngp13.phx.gbl...

quote:

---

> Is the database set to 6.5 compability?
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:%23lTlDlc6DHA.2576@.TK2MSFTNGP11.phx.gbl...
150[QUOTE]
gettign[QUOTE]
>

---

|||That's the problem. ALTER support was introduced with v7.
"David A. Beck" <davidb@.beckb.com> wrote in message
news:umAJ7yc6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Yes it is.
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:uUJU%23sc6DHA.2908@.tk2msftngp13.phx.gbl...
> 150
> gettign
>

---

|||Scott, thanks. The light went on when I read your question. I did change it
and it works. Thanks so much.
David A. Beck
"Scott Morris" <bogus@.bogus.com> wrote in message
news:epMcOFd6DHA.1752@.tk2msftngp13.phx.gbl...

quote:

---

> That's the problem. ALTER support was introduced with v7.
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:umAJ7yc6DHA.2628@.TK2MSFTNGP10.phx.gbl...
from[QUOTE]
>

---

|||what errors do you get?
"David A. Beck" <davidb@.beckb.com> wrote in message
news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I used this command on SQL2K to change the length of a column from 150 to
> 200
> ALTER TABLE REPFAX ALTER COLUMN EMAIL VARCHAR(200)
> I've tried all vesions and perversions of it on SQL7 but keep gettign the
> error that there is something wrong around COLUMN
> David A. Beck
>

---

|||Richard:
Please read further up the string. I already answered that. The problem was
that the database was set to 6.5 compatability. Thanks.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uzq1qmm6DHA.2720@.TK2MSFTNGP09.phx.gbl...

quote:

---

> what errors do you get?
> "David A. Beck" <davidb@.beckb.com> wrote in message
> news:OeSw$Oc6DHA.2572@.TK2MSFTNGP09.phx.gbl...
to[QUOTE]
the[QUOTE]
>

---

sql

Allowing Users to Filter on a Report Column

I'm intending to email this report I've built in Excel format. Before this report was switched to SQL Reporting Services, it was hand built by someone who added in filtering in the spreadsheet. Is it possible to add that in with SQL Reporting Services? If so, how?

Thanks!

Interactive filter is not currently supported. However, you can use report parameter to simulate this.

1. Add a parameter to the report that passes in the filter value.

2. Define a drillthrough action on the column header where you want to let the user click to filter. Point the drillthrough report to be the same report.

allowing outside party to access database

What would be the best way to allow a third party to have read only access to
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
Dan D.
Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:

> What would be the best way to allow a third party to have read only access to
> part of our database. We both collect do not email information and they want
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.
|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
[vbcol=seagreen]
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:

allowing outside party to access database

What would be the best way to allow a third party to have read only access to
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
--
Dan D.Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:
> What would be the best way to allow a third party to have read only access to
> part of our database. We both collect do not email information and they want
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
> > What would be the best way to allow a third party to have read only access to
> > part of our database. We both collect do not email information and they want
> > to be able to see what we have and load it into their system to supplement
> > what they have collected.
> >
> > We're thinking about a readonly view of certain fields in one table. Would
> > using xml be more secure than setting up a linked server? Or is there a
> > better way?
> >
> > Thanks,
> > --
> > Dan D.|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > More secure and easier. If they really need linked server access, put data
> > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > don't want them in your main DB.
> >
> > Regards
> > Mike
> >
> > "Dan D." wrote:
> >
> > > What would be the best way to allow a third party to have read only access to
> > > part of our database. We both collect do not email information and they want
> > > to be able to see what we have and load it into their system to supplement
> > > what they have collected.
> > >
> > > We're thinking about a readonly view of certain fields in one table. Would
> > > using xml be more secure than setting up a linked server? Or is there a
> > > better way?
> > >
> > > Thanks,
> > > --
> > > Dan D.|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
> > I'm not sure I understand. Are you saying that the linked server is more
> > secure and easier? Or that something else is more secure and easier but if
> > they have to have a linked server to put it into a separate db?
> >
> > Thanks,
> >
> > Dan D.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > More secure and easier. If they really need linked server access, put data
> > > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > > don't want them in your main DB.
> > >
> > > Regards
> > > Mike
> > >
> > > "Dan D." wrote:
> > >
> > > > What would be the best way to allow a third party to have read only access to
> > > > part of our database. We both collect do not email information and they want
> > > > to be able to see what we have and load it into their system to supplement
> > > > what they have collected.
> > > >
> > > > We're thinking about a readonly view of certain fields in one table. Would
> > > > using xml be more secure than setting up a linked server? Or is there a
> > > > better way?
> > > >
> > > > Thanks,
> > > > --
> > > > Dan D.|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
> > I'm not sure I understand. Are you saying that the linked server is more
> > secure and easier? Or that something else is more secure and easier but if
> > they have to have a linked server to put it into a separate db?
> >
> > Thanks,
> >
> > Dan D.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > More secure and easier. If they really need linked server access, put data
> > > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > > don't want them in your main DB.
> > >
> > > Regards
> > > Mike
> > >
> > > "Dan D." wrote:
> > >
> > > > What would be the best way to allow a third party to have read only access to
> > > > part of our database. We both collect do not email information and they want
> > > > to be able to see what we have and load it into their system to supplement
> > > > what they have collected.
> > > >
> > > > We're thinking about a readonly view of certain fields in one table. Would
> > > > using xml be more secure than setting up a linked server? Or is there a
> > > > better way?
> > > >
> > > > Thanks,
> > > > --
> > > > Dan D.

allowing outside party to access database

What would be the best way to allow a third party to have read only access t
o
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
--
Dan D.Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:

> What would be the best way to allow a third party to have read only access
to
> part of our database. We both collect do not email information and they wa
nt
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
>|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
[vbcol=seagreen]
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that c
an
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
>|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that c
an
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
>

allowing non sysadmin user to send email with attachments

i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?

any suggestion will be appreciated. thanks.

Which version of SQL Server are you using?|||i'm using sql server 2005 sp2|||

One possibility could be using impersonation (EXECUTE AS) on the module and/or digital signatures. The idea would be to create a wrapper under your control that would escalate the privileges and call sp_snd_dbmail.

Personally I am not quite familiar with the DB mail functionally, but based on the fact that the invocation is via a SP, I am not sure if digital signatures will be enough in this particular case.

Digital signatures should allow the caller to execute the module, but the signature would be dropped in order to execute the code on sp_send_dbmail; if the SP has internal checks to verify the calling context the call will be terminated. Impersonating a privileged user (member of sysadmin in this case) and using a digital signature to vouch for the token at server scope (the signing certificate would require AUTHENTICATE SERVER) should be an option if the digital signature by itself is not enough in this scenario.

I would like to emphasize that because the escalation (either via signature or via EXECUTE AS) is going to be to sysadmin, you need to be extremely careful and make sure your code is safe and there are no possibilities of running arbitrary code (i.e. no SQL injection is possible, validate any input, make sure the parameters to sp_send_dbmail are safe, limit the escalated functionality to the essential minimum, etc.) by anyone who has permission to execute this module.

For more detailed information please refer to BOL:

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

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

Let us know if you have further questions or comments

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I forgot to include Laurentiu's article on cross-DB as a reference: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine