Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

sql

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

MAPI Mail Client for SQL Mail?

Are there any free MAPI mail clients that I could use with an Internet mail
service (POP3/SMTP) so I could set up SQL Mail?
The Outlook Express that came with my dedicated server doesn't seem to do
the trick, and it's not easy to install Outlook on a dedicated server at a
hosting site where I can't just pop in a CD.
Thanks for any help.Don,
I am not sure I understand what you need to do. If this is SQL2005 you can
use Database mail to access any SMTP server. If you are on 2000 you might
want to check this out:
http://www.sqldev.net/xp/xpsmtp.htm
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
> Are there any free MAPI mail clients that I could use with an Internet
> mail
> service (POP3/SMTP) so I could set up SQL Mail?
> The Outlook Express that came with my dedicated server doesn't seem to do
> the trick, and it's not easy to install Outlook on a dedicated server at a
> hosting site where I can't just pop in a CD.
> Thanks for any help.
>|||Didn't know MVPs hung around on weekends ;) Thanks.
I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
backup) runs successfully (or more importantly fails) that I get an email.
Or if a CHECKDB identifies problems?
I've seen 'xpsmtp' but it seems a little complicated, and requires extra
non-intuitive steps when setting up jobs.
I just thought there might be a downloadable MAPI mail client somewhere (not
necessarily free) that would let me use SQL Mail as if I had Outlook
installed and could use email notification with jobs as easily as I could if
I had Outlook installed.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
> Don,
> I am not sure I understand what you need to do. If this is SQL2005 you can
> use Database mail to access any SMTP server. If you are on 2000 you might
> want to check this out:
> http://www.sqldev.net/xp/xpsmtp.htm
>
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
> > Are there any free MAPI mail clients that I could use with an Internet
> > mail
> > service (POP3/SMTP) so I could set up SQL Mail?
> >
> > The Outlook Express that came with my dedicated server doesn't seem to
do
> > the trick, and it's not easy to install Outlook on a dedicated server at
a
> > hosting site where I can't just pop in a CD.
> >
> > Thanks for any help.
> >
> >
>|||Setting up a MAPI mail client such as Exchange or Lotus Notes that will be
trouble-free and won't require you to pop-up a CD? Didn't know that such a
thing existed. Instead of MAPI, try using a SMTP client instead for sending
your warnings:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://www.sqlteam.com/item.asp?ItemID=5003
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>> Don,
>> I am not sure I understand what you need to do. If this is SQL2005 you
>> can
>> use Database mail to access any SMTP server. If you are on 2000 you might
>> want to check this out:
>> http://www.sqldev.net/xp/xpsmtp.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
>> > Are there any free MAPI mail clients that I could use with an Internet
>> > mail
>> > service (POP3/SMTP) so I could set up SQL Mail?
>> >
>> > The Outlook Express that came with my dedicated server doesn't seem to
> do
>> > the trick, and it's not easy to install Outlook on a dedicated server
>> > at
> a
>> > hosting site where I can't just pop in a CD.
>> >
>> > Thanks for any help.
>> >
>> >
>>
>|||Unfortunately setting up Mapi with SQL2000 can be quite troublesome and
using SMTP for job notification can involve a lot of extra work as well. If
you are willing to spend a few dollars your life could be made much easier
with a tool such as SQL Sentry from www.sqlsentry.net. It does a lot more
than just monitoring job status and can save a lot of aggravation.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:evpT4hWyGHA.1256@.TK2MSFTNGP04.phx.gbl...
> Didn't know MVPs hung around on weekends ;) Thanks.
> I do have Win2K. What I'd like to do is, when a SQL Server Agent job (e.g.
> backup) runs successfully (or more importantly fails) that I get an email.
> Or if a CHECKDB identifies problems?
> I've seen 'xpsmtp' but it seems a little complicated, and requires extra
> non-intuitive steps when setting up jobs.
> I just thought there might be a downloadable MAPI mail client somewhere
> (not
> necessarily free) that would let me use SQL Mail as if I had Outlook
> installed and could use email notification with jobs as easily as I could
> if
> I had Outlook installed.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:upltlVWyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>> Don,
>> I am not sure I understand what you need to do. If this is SQL2005 you
>> can
>> use Database mail to access any SMTP server. If you are on 2000 you might
>> want to check this out:
>> http://www.sqldev.net/xp/xpsmtp.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:e0PMxMWyGHA.3464@.TK2MSFTNGP03.phx.gbl...
>> > Are there any free MAPI mail clients that I could use with an Internet
>> > mail
>> > service (POP3/SMTP) so I could set up SQL Mail?
>> >
>> > The Outlook Express that came with my dedicated server doesn't seem to
> do
>> > the trick, and it's not easy to install Outlook on a dedicated server
>> > at
> a
>> > hosting site where I can't just pop in a CD.
>> >
>> > Thanks for any help.
>> >
>> >
>>
>sql

MAPI email service for SQL 7.0

Hello,
We recently set up our SQL 7.0 server (on Win2k) to email us on
different things (mainly results for Database Maintenance Plans). We
went through the process of installing Outlook and configuring SQL to
use its MAPI profile, etc., and it works fine. But my question is, is
there an alternative to using Outlook? I know there's Netscape and
Eudora, but an ideal solution would be a lightweight app that would
run as a service, that would send mail for its MAPI profile. Does
such a thing exist? The current setup works but is kind of
cumbersome. Sending email by SMTP would be even better, but so far we
haven't found anything that can do that, either, that interfaces with
SQL server's Maintenance Plans, etc.
Thanks for all feedback.
--
Chris> cumbersome. Sending email by SMTP would be even better, but so far we
> haven't found anything that can do that, either, that interfaces with
> SQL server's Maintenance Plans, etc.
http://www.aspfaq.com/2403
Not sure why xp_smtp_sendmail would be discarded simply because it doesn't
interface directly with maintenance plans (how often are you modifying these
in such a way that however you set them up will change?). What I do is
manually add a step to the job, which calls xp_smtp_sendmail (among other
things), and make that the last step. Previous steps move to the last step
on error. And the last step before the sendmail step just completes on
success, and moves to the "last" step on failure.|||Forget the SQL Server built in MAPI. It stinks and has a bunch of security
holes. Plus, it's hard to configure.
If you want to simply send e-mail from SQL Server using SMTP, download this
little gem. Simply copy the .DLL to your binary directory, registry the
X_SP and you're off.
Add "smarthost" to your hosts file if you wish to have it use the default IP
from here. Otherwise, you'll have to pass it the TCP/IP of your mail
server as a parameter. This has built in SMTP Agent.
http://www.sqldev.net/xp/xpsmtp.htm
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > cumbersome. Sending email by SMTP would be even better, but so far we
> > haven't found anything that can do that, either, that interfaces with
> > SQL server's Maintenance Plans, etc.
> http://www.aspfaq.com/2403
> Not sure why xp_smtp_sendmail would be discarded simply because it doesn't
> interface directly with maintenance plans (how often are you modifying
these
> in such a way that however you set them up will change?). What I do is
> manually add a step to the job, which calls xp_smtp_sendmail (among other
> things), and make that the last step. Previous steps move to the last
step
> on error. And the last step before the sendmail step just completes on
> success, and moves to the "last" step on failure.
>|||Thanks for all of the input. I have downloaded & installed XPSMTP and
it works great. The reason I originally inquired about easy interface
with Maintenance Plans was for other uses to easily create/modify new
Maintenance plans, and set up email easily.
Thanks again,
--
Chris
"David M" <scandal123@.cox.net> wrote in message news:<q8mlb.200840$%h1.197478@.sccrnsc02>...
> Forget the SQL Server built in MAPI. It stinks and has a bunch of security
> holes. Plus, it's hard to configure.
> If you want to simply send e-mail from SQL Server using SMTP, download this
> little gem. Simply copy the .DLL to your binary directory, registry the
> X_SP and you're off.
> Add "smarthost" to your hosts file if you wish to have it use the default IP
> from here. Otherwise, you'll have to pass it the TCP/IP of your mail
> server as a parameter. This has built in SMTP Agent.
> http://www.sqldev.net/xp/xpsmtp.htm
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > cumbersome. Sending email by SMTP would be even better, but so far we
> > > haven't found anything that can do that, either, that interfaces with
> > > SQL server's Maintenance Plans, etc.
> >
> > http://www.aspfaq.com/2403
> >
> > Not sure why xp_smtp_sendmail would be discarded simply because it doesn't
> > interface directly with maintenance plans (how often are you modifying
> these
> > in such a way that however you set them up will change?). What I do is
> > manually add a step to the job, which calls xp_smtp_sendmail (among other
> > things), and make that the last step. Previous steps move to the last
> step
> > on error. And the last step before the sendmail step just completes on
> > success, and moves to the "last" step on failure.
> >
> >|||Is there any concern for the stability of the xpsmtp extended proc since it
runs in the same memory space as sql server?
"Chris Szilagyi" <chris@.groupinfo.com> wrote in message
news:2aab79d7.0310281216.1ee8bc38@.posting.google.com...
> Thanks for all of the input. I have downloaded & installed XPSMTP and
> it works great. The reason I originally inquired about easy interface
> with Maintenance Plans was for other uses to easily create/modify new
> Maintenance plans, and set up email easily.
> Thanks again,
> --
> Chris
> "David M" <scandal123@.cox.net> wrote in message
news:<q8mlb.200840$%h1.197478@.sccrnsc02>...
> > Forget the SQL Server built in MAPI. It stinks and has a bunch of
security
> > holes. Plus, it's hard to configure.
> >
> > If you want to simply send e-mail from SQL Server using SMTP, download
this
> > little gem. Simply copy the .DLL to your binary directory, registry the
> > X_SP and you're off.
> >
> > Add "smarthost" to your hosts file if you wish to have it use the
default IP
> > from here. Otherwise, you'll have to pass it the TCP/IP of your mail
> > server as a parameter. This has built in SMTP Agent.
> >
> > http://www.sqldev.net/xp/xpsmtp.htm
> >
> >
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > cumbersome. Sending email by SMTP would be even better, but so far
we
> > > > haven't found anything that can do that, either, that interfaces
with
> > > > SQL server's Maintenance Plans, etc.
> > >
> > > http://www.aspfaq.com/2403
> > >
> > > Not sure why xp_smtp_sendmail would be discarded simply because it
doesn't
> > > interface directly with maintenance plans (how often are you modifying
> > these
> > > in such a way that however you set them up will change?). What I do
is
> > > manually add a step to the job, which calls xp_smtp_sendmail (among
other
> > > things), and make that the last step. Previous steps move to the last
> > step
> > > on error. And the last step before the sendmail step just completes
on
> > > success, and moves to the "last" step on failure.
> > >
> > >|||Is there any concern about the stability of this extended proc since it runs
in the same mem space as sqlserver
"Chris Szilagyi" <chris@.groupinfo.com> wrote in message
news:2aab79d7.0310281216.1ee8bc38@.posting.google.com...
> Thanks for all of the input. I have downloaded & installed XPSMTP and
> it works great. The reason I originally inquired about easy interface
> with Maintenance Plans was for other uses to easily create/modify new
> Maintenance plans, and set up email easily.
> Thanks again,
> --
> Chris
> "David M" <scandal123@.cox.net> wrote in message
news:<q8mlb.200840$%h1.197478@.sccrnsc02>...
> > Forget the SQL Server built in MAPI. It stinks and has a bunch of
security
> > holes. Plus, it's hard to configure.
> >
> > If you want to simply send e-mail from SQL Server using SMTP, download
this
> > little gem. Simply copy the .DLL to your binary directory, registry the
> > X_SP and you're off.
> >
> > Add "smarthost" to your hosts file if you wish to have it use the
default IP
> > from here. Otherwise, you'll have to pass it the TCP/IP of your mail
> > server as a parameter. This has built in SMTP Agent.
> >
> > http://www.sqldev.net/xp/xpsmtp.htm
> >
> >
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > cumbersome. Sending email by SMTP would be even better, but so far
we
> > > > haven't found anything that can do that, either, that interfaces
with
> > > > SQL server's Maintenance Plans, etc.
> > >
> > > http://www.aspfaq.com/2403
> > >
> > > Not sure why xp_smtp_sendmail would be discarded simply because it
doesn't
> > > interface directly with maintenance plans (how often are you modifying
> > these
> > > in such a way that however you set them up will change?). What I do
is
> > > manually add a step to the job, which calls xp_smtp_sendmail (among
other
> > > things), and make that the last step. Previous steps move to the last
> > step
> > > on error. And the last step before the sendmail step just completes
on
> > > success, and moves to the "last" step on failure.
> > >
> > >|||I use it extensively in mission critical apps and have yet to notice any
issues whatsoever.
"Rohan Balraj" <rohanbalraj@.bellsouth.net> wrote in message
news:wsGnb.77882$W77.8168@.bignews6.bellsouth.net...
> Is there any concern for the stability of the xpsmtp extended proc since
it
> runs in the same memory space as sql server?|||> Is there any concern for the stability of the xpsmtp extended proc since it
> runs in the same memory space as sql server?
The same applies for xp_sendmail. It is also an extended stored procedure. Personally, I have the
greatest confidence in the developer of xp_smtp_sendmail.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rohan Balraj" <rohanbalraj@.bellsouth.net> wrote in message
news:wsGnb.77882$W77.8168@.bignews6.bellsouth.net...
> Is there any concern for the stability of the xpsmtp extended proc since it
> runs in the same memory space as sql server?
> "Chris Szilagyi" <chris@.groupinfo.com> wrote in message
> news:2aab79d7.0310281216.1ee8bc38@.posting.google.com...
> > Thanks for all of the input. I have downloaded & installed XPSMTP and
> > it works great. The reason I originally inquired about easy interface
> > with Maintenance Plans was for other uses to easily create/modify new
> > Maintenance plans, and set up email easily.
> >
> > Thanks again,
> > --
> > Chris
> >
> > "David M" <scandal123@.cox.net> wrote in message
> news:<q8mlb.200840$%h1.197478@.sccrnsc02>...
> > > Forget the SQL Server built in MAPI. It stinks and has a bunch of
> security
> > > holes. Plus, it's hard to configure.
> > >
> > > If you want to simply send e-mail from SQL Server using SMTP, download
> this
> > > little gem. Simply copy the .DLL to your binary directory, registry the
> > > X_SP and you're off.
> > >
> > > Add "smarthost" to your hosts file if you wish to have it use the
> default IP
> > > from here. Otherwise, you'll have to pass it the TCP/IP of your mail
> > > server as a parameter. This has built in SMTP Agent.
> > >
> > > http://www.sqldev.net/xp/xpsmtp.htm
> > >
> > >
> > >
> > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > > cumbersome. Sending email by SMTP would be even better, but so far
> we
> > > > > haven't found anything that can do that, either, that interfaces
> with
> > > > > SQL server's Maintenance Plans, etc.
> > > >
> > > > http://www.aspfaq.com/2403
> > > >
> > > > Not sure why xp_smtp_sendmail would be discarded simply because it
> doesn't
> > > > interface directly with maintenance plans (how often are you modifying
> > > these
> > > > in such a way that however you set them up will change?). What I do
> is
> > > > manually add a step to the job, which calls xp_smtp_sendmail (among
> other
> > > > things), and make that the last step. Previous steps move to the last
> > > step
> > > > on error. And the last step before the sendmail step just completes
> on
> > > > success, and moves to the "last" step on failure.
> > > >
> > > >
>|||It's a shame that something that seems so trivial wasn't done by Microsoft.
Something like XPSMTP originally instead of xp_sendmail would have made
things much more easier for SQL Admins--especially when only needing to send
outgoing mail from SQL Server. Who knows -- maybe Yukon will have a few
surprises...
"Rohan Balraj" <rohanbalraj@.bellsouth.net> wrote in message
news:ItGnb.77890$W77.37198@.bignews6.bellsouth.net...
> Is there any concern about the stability of this extended proc since it
runs
> in the same mem space as sqlserver
> "Chris Szilagyi" <chris@.groupinfo.com> wrote in message
> news:2aab79d7.0310281216.1ee8bc38@.posting.google.com...
> > Thanks for all of the input. I have downloaded & installed XPSMTP and
> > it works great. The reason I originally inquired about easy interface
> > with Maintenance Plans was for other uses to easily create/modify new
> > Maintenance plans, and set up email easily.
> >
> > Thanks again,
> > --
> > Chris
> >
> > "David M" <scandal123@.cox.net> wrote in message
> news:<q8mlb.200840$%h1.197478@.sccrnsc02>...
> > > Forget the SQL Server built in MAPI. It stinks and has a bunch of
> security
> > > holes. Plus, it's hard to configure.
> > >
> > > If you want to simply send e-mail from SQL Server using SMTP, download
> this
> > > little gem. Simply copy the .DLL to your binary directory, registry
the
> > > X_SP and you're off.
> > >
> > > Add "smarthost" to your hosts file if you wish to have it use the
> default IP
> > > from here. Otherwise, you'll have to pass it the TCP/IP of your mail
> > > server as a parameter. This has built in SMTP Agent.
> > >
> > > http://www.sqldev.net/xp/xpsmtp.htm
> > >
> > >
> > >
> > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:uZvL2S0lDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > > cumbersome. Sending email by SMTP would be even better, but so
far
> we
> > > > > haven't found anything that can do that, either, that interfaces
> with
> > > > > SQL server's Maintenance Plans, etc.
> > > >
> > > > http://www.aspfaq.com/2403
> > > >
> > > > Not sure why xp_smtp_sendmail would be discarded simply because it
> doesn't
> > > > interface directly with maintenance plans (how often are you
modifying
> > > these
> > > > in such a way that however you set them up will change?). What I do
> is
> > > > manually add a step to the job, which calls xp_smtp_sendmail (among
> other
> > > > things), and make that the last step. Previous steps move to the
last
> > > step
> > > > on error. And the last step before the sendmail step just completes
> on
> > > > success, and moves to the "last" step on failure.
> > > >
> > > >
>|||> Who knows -- maybe Yukon will have a few surprises...
There has been a bit of rumbling about (and definitely a lot of screaming
and begging for) a new e-mail system, but it's way too early to tell if it
will be implemented at all, and if so, whether it will make it into Yukon,
or Yukon + 1, or Yukon + n... never mind what it will look like, and whether
it will support SMTP and POP3, or just SMTP, etc. As you said, maybe there
will be a few surprises... we'll have to wait for subsequent bits, I
suppose. :-)

many-to-many parent-child relationship in the dimension hierarchy

This novice would like your assistance to solve a problem. The goal is to build a Data mart for use in a cube. The cube would have a Service Fact related to the Dimension of Personnel and Assets. The Measures would be:

Provision of Assets by Departments

Availability of Assets by Departments or Service

Volume of Support Calls by Departments

The model has one major problem: a many-to-many parent-child relationship in the hierarchy of the Asset Dimension. The hierarchy within the Dimension is

Bundle

Service

Assets

And the many-to-many relationship resides between Assets & Service.I wish to know how best to handle this situation; create a bridge table or create a Service Dimension. All advice is welcomed.

What is the business process you are modeling in your fact table? The description of the measures is a little confusing to me.

Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?

Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?

Thanks,
Bryan

|||The main objective is to create a cube on IT Support for performance reporting against SLAs, with a secondary objective being to expose the use of IT within the organisation. The high level categories of measures are: provisioning, availability, volume, and minutes. These would translate into measurement exposing the aggregated number of Assets provisioned; the percentage an Asset was available; the aggregated number of support calls received for an Asset; the minutes taken to close a support call on an Assest. Basically, sliced, diced and rolled up and down by organisation section, business unit, IT bundle and service. This is datamart design based on this information:

Code Snippet

Assets Dim -> Support Fact <- Personnel Dim
AssetID AssetID PersonnelID
Class PersonnelID Job
Service %Available Business Unit
Bundle Provisioned Assets Section
Call volume
Call minutes

The Dimensions have been model to the lowest level of granularity. The Personnel Dimension offers no problems with the hierarchy; a staff member only belongs to one job, business unit & section. This is not the case for Assets. All Assets are assigned to a member of staff. An Asset is a member of a class --mobile, pc, server,...--, one or many services and those services are member of one bundle.

"Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?"

Yes. The IT Business Unit offers 5 bundles of services. Each bundle is comprise of mutually exclusive services. An example would be the End User Computing Bundle comprising of Desk Support, Printing & Fax, etc.

"Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?"

One or multiple through the asset. Each Asset is tired to at lest one service. Assets such as servers offer more than one service.

I appreciate your response and am happy to supply additional information.
|||

According to Kimball methodology, a fact table should represent a single business process. So in this data mart, you would have a fact table for the handling of service tickets, another one for provisioning, another one for asset uptime/availability, and so on and so on. You might bring all this data together in a report using conformed dimensions between the fact tables. I'd recommend taking a look into that. It will make your maintanance of the data much, much easier. (You can easily tie all this data together in your olap cube so your users won't have to jump between fact tables aka measure groups.)

When you do that, I think you will find that some facts relate directly to assets while others relate directly to the services.

Still, you will have assets providing services and need to model that relationship. I'd recommend building a service group table that ties together the services being offered. Then associate an asset with a service group. Here's a rough example:

Asset (AssetID, ServiceGroupID, ..., StartDate, EndDate)

ServiceGroup (ServiceGroupID, ....)

ServiceGroupServiceJunction (ServiceGroupID, ServiceID, ....)

Service (ServiceID, ....)

So, if you had a fact associated with an asset, you would be able to identify what services are provided by that asset. If you are working with Asset as a Type 2 slowly changing dimension, there would be different records for an asset in the Asset table based on the ServiceGroups it was associated with across time.

If you have a copy of "The Microsoft Data Warehouse Toolkit", this is covered on page 60. (I think this is covered in "The Data Warehouse Toolkit" as well but someone has borrowed by copy.)

Hope that helps,
Bryan

|||Bryan,

Thanks for your suggestions and insight. I will have a read of the books mentioned and most likely return with a few questions...hopefully your generosity will continue to provide answers.

Ian
|||

Assuming that you're using AS 2005, the many-many relationship between Assets and Services could be modelled, if separate Asset and Service dimensions are set up. There is 1 measure that isn't just additive, and hence may need further fields in the fact table: "the percentage an Asset was available". Generally, availability is a ratio like available to total time. Even in the simplest scenario, the numerator and denominator would be separately summed (say across Assets in this case), then their ratio taken. So, if the fact table has either "%Available" or "AvailableTime", and "TotalTime", the aggregate "%Available" could be computed.

The schema for AS 2005 could then be something like:

PersonnelDim SupportFact AssetDim Asset<->ServiceBridge ServiceDim

AssetID --> AssetID <-- AssetID

PersonnelID <-- PersonnelID Asset ServiceID --> ServiceID

Job AvailTime Class Service

Business Unit TotalTime Bundle

Section Provisioned

Call Volume

Call Minutes

There would be a Measure Group on SupportFact, with "sum" measures: AvailTime, TotalTime, Provisioned, Call Volume, Call Minutes.

["%Available] could then be defined as: AvailTime / TotalTime.

The Personnel and Asset dimensions would have a regular relation to the SupportFact Measure Group.

An intermediate Measure Group is then defined on the Asset<->ServiceBridge table, to which Asset and Service dimensions are related.

This would allow the Service dimension to have a many-many relation to the SupportFact Measure Group, via this intermediate MG.

Many Queues and one Activation Procedure for that queues.

Hi, All!

Could you please help me in my problem?

I develop Service Broker applcation, SB has 20 queues, also I have one Activation Procedure which carries out following actions:

CREATE PROCEDURE proc_ms_Activation

AS

BEGIN

WAITFOR (RECEIVE TOP(1) message_type_name, message_body, conversation_handle, message_id FROM Queue1)

-- process message, save statisctisc and etc

END

Problem in that: when I execute command WAITFOR (RECEIVE TOP (1) message_type_name, message_body, conversation_handle, message_id FROM Queue1) I should specify a name concrete Queue, for example "... FROM Queue1 ". I would like to use one procedure for processing all Queue.

How I can design my application that one Activation Procedure processed messages from all Queues?

Thank a lot for help.

You can figure out what queue you were activated for by looking in sys.dm_broker_activated_tasks and filter for the current session (spid = @.@.SPID). Then use database_id and queue_id to lookup the queue name is sys.service_queues and execute the WAITFOR (RECEIVE... ) as dynamic SQL.

BTW, If you run into EXECUTE AS context restrictions (like inability to see the records in servel level views) see this article: http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx Simples fix is to mark the database trustworthy.

HTH,
~ Remus

|||

Hi Svitlana!

What's the reason that you have one activated stored procedure for the whole 20 queues? Because when you get different message types on each of the 20 queues, then it makes (for me) no sense to use just one activated stored procedure that handles all message types. Why you don't use one activated stored procedure for each queue?

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Thanks for the shown interest, excuse that long did not answer.

Remus, thanks a lot for help! you post is very useful.

Klaus, allow me to explain what's the reason that I have one activated stored procedure for the 20 queues.

In my application only one custom message type. Stored Procedures identical and also differ only Queue name. It would be convenient to have one stored procedure for all queues.

Manually install sqlagent90.exe as a service

Is there a way to manually install sqlagent90.exe as a service?

I first have to ask, why? The SQL Agent is already a service and is installed that way when SQL Server is installed.

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

for more help.

|||

We found the problem. At least 3 of our servers were missing the "Start" key in the registry for the SQL Agent service.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2005]
"Start"=dword:00000002

Apparently, Microsoft is having issues with the proper installation of the SQL Agent service for SQL Server 2005.

There are several postings on the internet related to the installation of this service, but none addressed this problem.

|||

Thanks for this feedback. I had not ever seen this problem before. Something good to know.

Manually configuring SPN when SQL Service account is local non-adm

Hi,
We are running SQL Server 2005 with a local low-privileged service account.
I would like to register the SPN (manually) to enable kerberos authentication.
The domain admin gets the error message "Unable to locate account <Service
Account Name>" when running the setspn command.
I can only find examples for configuring the SPN when the SQL Service
account is a (non-admin) domain user account or a local administrator account.
Does anyone know whether it is possible or not to enable kerberos
authentication when running SQL Server 2005 with a local low-privileged
service account?
Thanks!
Kai A
No. A local account is not an Active Directory account.
-Sue
On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
<KaiA@.discussions.microsoft.com> wrote:

>Hi,
>We are running SQL Server 2005 with a local low-privileged service account.
>I would like to register the SPN (manually) to enable kerberos authentication.
>The domain admin gets the error message "Unable to locate account <Service
>Account Name>" when running the setspn command.
>I can only find examples for configuring the SPN when the SQL Service
>account is a (non-admin) domain user account or a local administrator account.
>Does anyone know whether it is possible or not to enable kerberos
>authentication when running SQL Server 2005 with a local low-privileged
>service account?
>Thanks!
>Kai A
|||Thank your very much for your answer!
However, I do not understand why one can register a local admin account in
AD and not a local low-privileged one? A local admin account is not an AD
account either?
This describes how one can register a local admin sql server service account
in AD:
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx :
"d. If your sql server is running under a local machine admin account, you
can (...)run setspn under your domain credential to add the SPN."
Thanks!
Kai A
"Sue Hoegemeier" wrote:

> No. A local account is not an Active Directory account.
> -Sue
> On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
> <KaiA@.discussions.microsoft.com> wrote:
>
>

Manually configuring SPN when SQL Service account is local non-adm

Hi,
We are running SQL Server 2005 with a local low-privileged service account.
I would like to register the SPN (manually) to enable kerberos authenticatio
n.
The domain admin gets the error message "Unable to locate account <Service
Account Name>" when running the setspn command.
I can only find examples for configuring the SPN when the SQL Service
account is a (non-admin) domain user account or a local administrator accoun
t.
Does anyone know whether it is possible or not to enable kerberos
authentication when running SQL Server 2005 with a local low-privileged
service account?
Thanks!
Kai ANo. A local account is not an Active Directory account.
-Sue
On Tue, 13 Feb 2007 06:01:02 -0800, Kai A
<KaiA@.discussions.microsoft.com> wrote:

>Hi,
>We are running SQL Server 2005 with a local low-privileged service account.
>I would like to register the SPN (manually) to enable kerberos authenticati
on.
>The domain admin gets the error message "Unable to locate account <Service
>Account Name>" when running the setspn command.
>I can only find examples for configuring the SPN when the SQL Service
>account is a (non-admin) domain user account or a local administrator accou
nt.
>Does anyone know whether it is possible or not to enable kerberos
>authentication when running SQL Server 2005 with a local low-privileged
>service account?
>Thanks!
>Kai A

Managing user rights

Hi,
how can I change user right to specific DB? This must be done without stopping and restarting the service! I know this is just a basic thing to do, but I′n newbie so.... And if you are so kind that you answer, can you be quite specific with your answer.
Otherwise my boss will be behind my desk and asking me, why the whole company cant sale any products... ;() Thank You.
Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but In newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Managing user rights

Hi
how can I change user right to specific DB? This must be done without stopping and restarting the service! I know this is just a basic thing to do, but I´n newbie so.... And if you are so kind that you answer, can you be quite specific with your answer. Otherwise my boss will be behind my desk and asking me, why the whole company cant sale any products... ;() Thank You.Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but I´n newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Managing user rights

Hi,
how can I change user right to specific DB? This must be done without stoppi
ng and restarting the service! I know this is just a basic thing to do, but
I′n newbie so.... And if you are so kind that you answer, can you be quite
specific with your answer.
Otherwise my boss will be behind my desk and asking me, why the whole compan
y cant sale any products... ;() Thank You.Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but In newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Management Studio Very slow to open

Hi,

I've had this problem with a VM and now on the Live box. I installed Windows Server 2003 SP1, then install SQL server 2005 and applied service pack 1.

As an administrator Management Studio takes approximately 2 seconds to open. However, if I log in to windows with any other local user account Manangement Studio takes approximately 1.5 minutes to open.

The whole interface is slow unless I use the administrator account to log into windows.

If anyone has a solution I would be very greatful as this is a new build for our production environment.

Thanks in advance.

Iain

Not sure if this will help, but if you have a look at internet explorer, on tools, internet options, advanced, security, check for publishers certificate revocation. I believe the default is enabled (ticked). On out NON-INTERNET facing DB machines, we have disabled that, and see a significant improvement in SSMS 'opening' time. I have heard that SSMS 'calls home' to check soemthing, and that by disabling this option, it no longer does this.

As I have it, the reason it can take so long is that on a non-internet facing machine, it waits for a timeout there.... true or not true ? I'm not sure, but I do know we are seeing a significant improvement

|||

https://blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx

|||Thanks very much for post. I'm teaching it on XPsp2 machines and the timeout was about 10 mins. Very annoying and this fix sorted it straight away takes 1 second to open now. :)