Showing posts with label address. Show all posts
Showing posts with label address. Show all posts

Tuesday, March 27, 2012

embedded control characters

Beyond my control: I am finding control characters (likely tab) is
making its way into address fields of our operational system. This is
messing me up when I load the data into our warehouse w/ BCP (fields
get shifted).
Is the a nifty way to strip control characters from data?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> Beyond my control: I am finding control characters (likely tab) is
> making its way into address fields of our operational system. This is
> messing me up when I load the data into our warehouse w/ BCP (fields
> get shifted).
> Is the a nifty way to strip control characters from data?

UPDATE tbl
SET col = replace(col, char(9), ' ')
WHERE col LIKE '%' + char(9) + '%'

You could have to nest replace, if there are more characters you want
to kill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

Email validation in ReportManager

Setting up a timed subscription in Reporting Services works just fine on our
server when the email address is joe@.domain.com. The Report server is set
up pointing to our SMTP server and works like a champ. Reports go out and
our chosen format (PDF) is generated and all is good.
When we enter a non-standard Email in the TO field (a RightFax email
address), the timed event runs and gives us a status of "The e-mail address
of one or more recipients is not valid."
Our question is: How can we turn off email validation within reporting
services so it will connect to the SMTP server (Exchange)?
ThanksHi,
This issue is usually caused by the fact that Email Relay is not turned on.
You may see something like the following in the Reporting Services Logfiles:
ReportingServicesService!emailextension!b14!10/14/2004-08:20:06:: Error
sending mail, CDO error -2147220977, will not resend
ReportingServicesService!emailextension!b14!10/14/2004-08:20:06:: Error
sending email. System.Runtime.InteropServices.COMException (0x8004020F):
The server
rejected one or more recipient addresses. The server response was: 550
5.7.1 Unable to relay for xxx@.xxx.xxx
at ReportingServicesCDOInterop.MessageClass.Send()
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Noti
fication notification)
ReportingServicesService!notification!b14!10/14/2004-08:20:06::
Notification 02710147-2025-4ed0-9c58-b37f12b64a79 completed. Success:
False, Status: The e-mail
address of one or more recipients is not valid., DeliveryExtension: Report
Server
Emai
If this is the case, you need to turn on email relay on the SMTP server.
If the error in the Reporting Services log file is not the same, please let
me know the exact error message so that we could get a better idea of the
issue.
The log file is usually located at:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Logfiles
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Reply-To: "isicoder" <isicoder@.newsgroups.nospam>
| From: "isicoder" <isicoder@.newsgroups.nospam>
| Subject: Email validation in ReportManager
| Date: Fri, 27 Jan 2006 12:35:16 -0800
| Lines: 16
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OPD1vE4IGHA.424@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 67.96.36.252
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67707
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Setting up a timed subscription in Reporting Services works just fine on
our
| server when the email address is joe@.domain.com. The Report server is
set
| up pointing to our SMTP server and works like a champ. Reports go out
and
| our chosen format (PDF) is generated and all is good.
|
| When we enter a non-standard Email in the TO field (a RightFax email
| address), the timed event runs and gives us a status of "The e-mail
address
| of one or more recipients is not valid."
|
| Our question is: How can we turn off email validation within reporting
| services so it will connect to the SMTP server (Exchange)?
|
| Thanks
|
|
|
|

Thursday, March 22, 2012

Email through SQL

I am trying to setup my sql to send automatic emails to different companies but I am not doing so well. The user puts an email address in the database and then presses a button (command button) that will execute a stored procedure xp_sendmail to a recepient. I need to know how to include read receipts and delivery receipts into the stored procedure, because right now its set up in his outlook as a rule to do this and I want to set it up in sql. The user deals with companies and these companies have several contacts with several email addresses which sql will store in a seperate table.Hiya Des...I'm sure others more knowledgeable will soon come to your rescue, but I don't think there are that kind of specific email options in xp_sendmail. However, Though I use the xp_sendmail quite a bit, I am no expert (as my recent thread posting shows).

HOWEVER, that said, perhaps you could send a "regular" email to your user from SQL Server, then set up some rules in Outlook to massage the necessary options and forward the email on to the appropriate customers? I know that's kinda a round-about way to get there, but it seems to me that it would work if nobody comes up with a definitive answer from SQL Server...

Just a thought

Email subscriptions: changing "from" address for different subscriptions on same s

I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?

It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).

Unfortunately this isn't really possible without doing some messing around. You can make a call to the RS WMI provider to change the SenderEmailAddress value, but you would have to synchronize the code making the WMI calls with the subscription times. Definitely a hack :-(.

Email subscriptions

Hello all,
I am trying to get some email subscriptions setup and I keep getting the
message "The e-mail address of one or more recipients is not valid". The
email address is valid and I am not sure what the problem is. If I set the
email address to a local account name (i.e Administrator) the subscription
works and e-mail shows up in the drop folder of the smtp server. Does anyone
know how I can get my subscriptions working to real email addresses and not
just local accounts? Any help would be appreciated.
Thanks,
JeffI have solved my own issue. All I needed to do was edit the SMTP server
settings to grant access to the server to relay through its own IP address.
Jeff
"Jeff McIntire" wrote:
> Hello all,
> I am trying to get some email subscriptions setup and I keep getting the
> message "The e-mail address of one or more recipients is not valid". The
> email address is valid and I am not sure what the problem is. If I set the
> email address to a local account name (i.e Administrator) the subscription
> works and e-mail shows up in the drop folder of the smtp server. Does anyone
> know how I can get my subscriptions working to real email addresses and not
> just local accounts? Any help would be appreciated.
> Thanks,
> Jeff|||How exactly did you do that? I have looked around for hours, you're the only
that has my exact same problem.
"Jeff McIntire" wrote:
> I have solved my own issue. All I needed to do was edit the SMTP server
> settings to grant access to the server to relay through its own IP address.
> Jeff
> "Jeff McIntire" wrote:
> > Hello all,
> >
> > I am trying to get some email subscriptions setup and I keep getting the
> > message "The e-mail address of one or more recipients is not valid". The
> > email address is valid and I am not sure what the problem is. If I set the
> > email address to a local account name (i.e Administrator) the subscription
> > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > know how I can get my subscriptions working to real email addresses and not
> > just local accounts? Any help would be appreciated.
> >
> > Thanks,
> >
> > Jeff|||All I did was go to the SMTP server properties and click on the access tab.
Then I clicked the relay button at the bottom of the page. Next I clicked on
add and inputted the IP address of my server. Then I clicked ok and made
sure the radio button labeled â'Only the list belowâ' was selected. That
solved my problem and I hope it solves yours as well.
Thanks,
Jeff
"DougM" wrote:
> How exactly did you do that? I have looked around for hours, you're the only
> that has my exact same problem.
> "Jeff McIntire" wrote:
> > I have solved my own issue. All I needed to do was edit the SMTP server
> > settings to grant access to the server to relay through its own IP address.
> >
> > Jeff
> >
> > "Jeff McIntire" wrote:
> >
> > > Hello all,
> > >
> > > I am trying to get some email subscriptions setup and I keep getting the
> > > message "The e-mail address of one or more recipients is not valid". The
> > > email address is valid and I am not sure what the problem is. If I set the
> > > email address to a local account name (i.e Administrator) the subscription
> > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > know how I can get my subscriptions working to real email addresses and not
> > > just local accounts? Any help would be appreciated.
> > >
> > > Thanks,
> > >
> > > Jeff|||Thanks, I tried re-installing Reporting Services to see if I could see that
screen but after choosing to modify components (as opposed to uninstall) it
went right through to finish...How did you get to the SMTP properties?. I
appreciate your help very much.
Regards,
DougM
"Jeff McIntire" wrote:
> All I did was go to the SMTP server properties and click on the access tab.
> Then I clicked the relay button at the bottom of the page. Next I clicked on
> add and inputted the IP address of my server. Then I clicked ok and made
> sure the radio button labeled â'Only the list belowâ' was selected. That
> solved my problem and I hope it solves yours as well.
> Thanks,
> Jeff
>
> "DougM" wrote:
> > How exactly did you do that? I have looked around for hours, you're the only
> > that has my exact same problem.
> >
> > "Jeff McIntire" wrote:
> >
> > > I have solved my own issue. All I needed to do was edit the SMTP server
> > > settings to grant access to the server to relay through its own IP address.
> > >
> > > Jeff
> > >
> > > "Jeff McIntire" wrote:
> > >
> > > > Hello all,
> > > >
> > > > I am trying to get some email subscriptions setup and I keep getting the
> > > > message "The e-mail address of one or more recipients is not valid". The
> > > > email address is valid and I am not sure what the problem is. If I set the
> > > > email address to a local account name (i.e Administrator) the subscription
> > > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > > know how I can get my subscriptions working to real email addresses and not
> > > > just local accounts? Any help would be appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Jeff|||I am sorry. Perhaps I should have clarified. The SMTP properties that I was
referring to were in the SMTP server in IIS itself. If you open up IIS and
navigate to the SMTP server and then right click on it and go to properties
you should find the screens that I was referring to.
Thanks,
Jeff
"DougM" wrote:
> Thanks, I tried re-installing Reporting Services to see if I could see that
> screen but after choosing to modify components (as opposed to uninstall) it
> went right through to finish...How did you get to the SMTP properties?. I
> appreciate your help very much.
> Regards,
> DougM
> "Jeff McIntire" wrote:
> > All I did was go to the SMTP server properties and click on the access tab.
> > Then I clicked the relay button at the bottom of the page. Next I clicked on
> > add and inputted the IP address of my server. Then I clicked ok and made
> > sure the radio button labeled â'Only the list belowâ' was selected. That
> > solved my problem and I hope it solves yours as well.
> >
> > Thanks,
> >
> > Jeff
> >
> >
> > "DougM" wrote:
> >
> > > How exactly did you do that? I have looked around for hours, you're the only
> > > that has my exact same problem.
> > >
> > > "Jeff McIntire" wrote:
> > >
> > > > I have solved my own issue. All I needed to do was edit the SMTP server
> > > > settings to grant access to the server to relay through its own IP address.
> > > >
> > > > Jeff
> > > >
> > > > "Jeff McIntire" wrote:
> > > >
> > > > > Hello all,
> > > > >
> > > > > I am trying to get some email subscriptions setup and I keep getting the
> > > > > message "The e-mail address of one or more recipients is not valid". The
> > > > > email address is valid and I am not sure what the problem is. If I set the
> > > > > email address to a local account name (i.e Administrator) the subscription
> > > > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > > > know how I can get my subscriptions working to real email addresses and not
> > > > > just local accounts? Any help would be appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Jeff

Wednesday, March 21, 2012

email problems with sql

I am trying to get a subscription to automatically send out an email to an
address outside of our company. It is giving me an error that says "The
e-mail address of one or more recipients is not valid." I do not have a
problem sending to addresses inside the company. Anyone have any ideas.I would carefully examine the email addresses to find any that are "not
valid".
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eCztpFFyGHA.5048@.TK2MSFTNGP05.phx.gbl...
>I am trying to get a subscription to automatically send out an email to an
>address outside of our company. It is giving me an error that says "The
>e-mail address of one or more recipients is not valid." I do not have a
>problem sending to addresses inside the company. Anyone have any ideas.
>

Email passwords

I am desigining an intranet site using vb.net/asp.net. The sql - users table has a username, password, e-mail address and date field (date is for when the user last updated there password). My login page etc is working fine, but what I want to do is when someone types in the wrong password, to send an email to the address from my users table giving them the correct password. I am not sure how to go about doing this and would appreciate any advice on this.I am desigining an intranet site using vb.net/asp.net. The sql - users table has a username, password, e-mail address and date field (date is for when the user last updated there password). My login page etc is working fine, but what I want to do is when someone types in the wrong password, to send an email to the address from my users table giving them the correct password. I am not sure how to go about doing this and would appreciate any advice on this.
Won't that be a bit dangerous and a security risk?
To E-mail the password when the user typed it wrong?
What if His/Her significant other or a friend or a neighbor takes a peek at the e-mail?
:mad:sql

Monday, March 19, 2012

Email From address needs to change

Does anyone know how to run reporting services so it can change the From
address for each report it sends. I need to use mutiple From addresses
depending what the report is.Could someone please reply to this posting as I need to know the answer as
well :-)
Thank you,
Lisa
"gamsql" wrote:
> Does anyone know how to run reporting services so it can change the From
> address for each report it sends. I need to use mutiple From addresses
> depending what the report is.

Sunday, March 11, 2012

email error

the site i am working on iswww.papertrailinvites.com

on the left when you enter your email address and hit submit i get this error:

Server Error in '/' Application.

Must declare the scalar variable "@.Email".

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Email".

Source Error:

Line 29: Line 30: conn.Open();Line 31: cmd.ExecuteNonQuery();Line 32: Line 33: }

My code is below:

protected void Button1_Click(object sender, EventArgs e) {using (SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["cardsConnectionString1"].ConnectionString)) {string email = Newsletter_Email.Text; SqlCommand cmd =new SqlCommand("INSERT INTO Email (EmailAddress) Values (@.Email)", conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@.Name", email); conn.Open(); cmd.ExecuteNonQuery(); } }

it looks like you mixed up "name" and "email"

try this:

protected void Button1_Click(object sender, EventArgs e) {using (SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["cardsConnectionString1"].ConnectionString)) {string email = Newsletter_Email.Text; SqlCommand cmd =new SqlCommand("INSERT INTO Email (EmailAddress) Values (@.Email)", conn); cmd.CommandType = CommandType.Text;//re-name param to match the cmd statement cmd.Parameters.AddWithValue("@.Email", email); conn.Open(); cmd.ExecuteNonQuery(); } }

|||

gers my code and i still get the error...

protectedvoid Button1_Click(object sender,EventArgs e)

{

using (SqlConnection conn =newSqlConnection(ConfigurationManager.ConnectionStrings["cardsConnectionString1"].ConnectionString)) {String newemail = Newsletter_Email.Text;

SqlCommand cmd =newSqlCommand("INSERT INTO Email (EmailAddress) Values (@.EmailAddress)", conn);

cmd.CommandType =

CommandType.Text;

cmd.Parameters.AddWithValue(

"@.EmailAdress", newemail);

conn.Open();

cmd.ExecuteNonQuery();

}

|||

double check your spelling.

your sql says: @.EmailAddress

your param says: @.EmailAdress

Email Deleivery

Hi,
I am using Emai Delievery Subscription feature of MSRS 2000. I have a
requirement where I can use different "From" address for Different group of
reports. Currently as I know there is only one from Adress I can use through
the RSReportServer.config file. I tried making a copy of the "Report Server
Email" delievery extension(with different From Adress) within the config
file and renaming it to "Report Server Email2" hoping that it will give me
one more drop down item with extension "Report Server Email2" in the
subscription window but it doesnt. Looks like the Extension Name is critical.
Is there a easy way to achieve this requirement . I am just avoiding to
write my own custom deleivery estension.
I just need to have different "from" adress for my different reports.
for eg SalesReports from :SalesAdmin@.MyCompany.com
and MarketingReports from :MarketingAdmin@.Mycompany.com.
Thanks in advance,
Cheers,
siajHi, siaj
what's the criteria that determines which address to use? why do you
need multiple addresses?
I am afraid that, from what you've expressed so far, you're gonna need
a custom email delivery extension. You can easily write that since
you're just gonna extend the existing extension, then add an extra
config file where you can setup the different email accounts, and roll
some code to look up the correct email address based on a specified
criteria (such as the report name, or folder, etc.).
regards,
Thiago Silva|||Thanks Thiago...
I guess I will have to go the Custom Delivery extension route !!
:)
Cheers,
siaj
"tafs7" wrote:
> Hi, siaj
> what's the criteria that determines which address to use? why do you
> need multiple addresses?
> I am afraid that, from what you've expressed so far, you're gonna need
> a custom email delivery extension. You can easily write that since
> you're just gonna extend the existing extension, then add an extra
> config file where you can setup the different email accounts, and roll
> some code to look up the correct email address based on a specified
> criteria (such as the report name, or folder, etc.).
> regards,
> Thiago Silva
>

Email based subscription - need to change the from address

I have multiple reports being sent to various clients. I need to change the
from address on each report based on report's category and client. Is there
anyway I can change the "from" attribute of email report? I checked the
RSServerConfig.xml, and looked the Email configuration section, but If I
change there its going to change for all the report, I want a different from
address for each of my report.
Please help.
vipul ShahThere is no way to change the from address per delivery. You can only
change it globally. You can however change the reply-to field.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vipul Shah" <VipulShah@.discussions.microsoft.com> wrote in message
news:3BA19CAC-2614-47AC-8A77-49ED8ACA5270@.microsoft.com...
>I have multiple reports being sent to various clients. I need to change
>the
> from address on each report based on report's category and client. Is
> there
> anyway I can change the "from" attribute of email report? I checked the
> RSServerConfig.xml, and looked the Email configuration section, but If I
> change there its going to change for all the report, I want a different
> from
> address for each of my report.
> Please help.
> vipul Shah|||That is very disappointing. I think it is very important feature to have in
reporting tool. Most of the report requirements would be for departmental
reports and each department would like to have their group email as a from
address. Is there anyway to have this feature in next release of reporting
services?
Also, how can I change "reply-to" address? Please advice.
Thanks
"Vipul Shah" wrote:
> I have multiple reports being sent to various clients. I need to change the
> from address on each report based on report's category and client. Is there
> anyway I can change the "from" attribute of email report? I checked the
> RSServerConfig.xml, and looked the Email configuration section, but If I
> change there its going to change for all the report, I want a different from
> address for each of my report.
> Please help.
> vipul Shah|||If you have Manage all subscription permission you will see the reply-to
field.
Changing the from clause is on the wish list already. Several customers
have asked for it.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vipul Shah" <VipulShah@.discussions.microsoft.com> wrote in message
news:C0664333-BFD0-498E-B333-D264B61A6C57@.microsoft.com...
> That is very disappointing. I think it is very important feature to have
> in
> reporting tool. Most of the report requirements would be for departmental
> reports and each department would like to have their group email as a from
> address. Is there anyway to have this feature in next release of
> reporting
> services?
> Also, how can I change "reply-to" address? Please advice.
> Thanks
> "Vipul Shah" wrote:
>> I have multiple reports being sent to various clients. I need to change
>> the
>> from address on each report based on report's category and client. Is
>> there
>> anyway I can change the "from" attribute of email report? I checked the
>> RSServerConfig.xml, and looked the Email configuration section, but If I
>> change there its going to change for all the report, I want a different
>> from
>> address for each of my report.
>> Please help.
>> vipul Shah

Friday, March 9, 2012

E-mail address of one or more recipients is not valid

We have setup Reporting Services on a Server 2003 box with SMTP intalled on
it. I am able to write a simple VB Script that uses CDO to send a e-mail out
to a certain address (let's say to john@.test.com). When I setup a
subscription using the same e-mail address I get the error "The e-mail
address of one or more recipients is not valid." Does anyone know why - if I
can write a 6 line CDO vbscript shouldn't SMTP be working?I've figured out what my problem is - apparently SQL Server Reporting
Services is not authenticating correctly. I had to change the settings on
the Relay Restictions on the Access tab for the SMTP Server Properties to be
allow relay through the virtual server with "All except the list below"
instead of the default "Only the list below". Seems like it should work with
the default setting since there is no authentication (anonymous access) but
looks like RS doesn't like that.
"John R" wrote:
> We have setup Reporting Services on a Server 2003 box with SMTP intalled on
> it. I am able to write a simple VB Script that uses CDO to send a e-mail out
> to a certain address (let's say to john@.test.com). When I setup a
> subscription using the same e-mail address I get the error "The e-mail
> address of one or more recipients is not valid." Does anyone know why - if I
> can write a 6 line CDO vbscript shouldn't SMTP be working?

Email Address in subscription

My companies email domain options for email address are diamondjo.com or
evangelinedowns.com.
When I set up a subscription is it possible to only put in the alias name
such as 'something' instead of somthing@.diamondjo.com? When I am in Outlook
creating an address these two domains are integrated into one address book.
I know each domain has there own exchange server.
If so, how.
Thanks.Hi Brian.
I think you're going to need to supply the fully qualified email address.
Reporting Services uses SMTP to send mail and it needs to the fully
qualified address to know where to relay the email to.
Is this a workaround to another problem?
-Tim
"Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
> My companies email domain options for email address are diamondjo.com or
> evangelinedowns.com.
> When I set up a subscription is it possible to only put in the alias name
> such as 'something' instead of somthing@.diamondjo.com? When I am in
> Outlook creating an address these two domains are integrated into one
> address book. I know each domain has there own exchange server.
> If so, how.
> Thanks.
>|||This is not a workaround. I am new to RS and figured that being a MS
product it would closely integrate with Exchange. I didn't want to be fully
qualifiying email if I didn't have to.
Thanks for your response.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:%23ldKm9GlGHA.4284@.TK2MSFTNGP05.phx.gbl...
> Hi Brian.
> I think you're going to need to supply the fully qualified email address.
> Reporting Services uses SMTP to send mail and it needs to the fully
> qualified address to know where to relay the email to.
> Is this a workaround to another problem?
> -Tim
> "Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
> news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
>> My companies email domain options for email address are diamondjo.com or
>> evangelinedowns.com.
>> When I set up a subscription is it possible to only put in the alias name
>> such as 'something' instead of somthing@.diamondjo.com? When I am in
>> Outlook creating an address these two domains are integrated into one
>> address book. I know each domain has there own exchange server.
>> If so, how.
>> Thanks.
>

Email address in stored Procedure parameters

Hi, i'm writing a simple sp that check if an email address already exist in
an AddressBook table.
This sp expect a parameters called @.Address.
When i call this sp and pass to it an email address which contains an at
character (@.) the stored procedure will return no values.
i think the @. alter the parameters value.
Whitout the @. char the sp works fine .
Can someone help me?
ThanksIf you post the stored procedure the chances of getting help will be
much, much better.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 14:59:01 -0700, Pantano Antonio
<PantanoAntonio@.discussions.microsoft.com> wrote:

>Hi, i'm writing a simple sp that check if an email address already exist in
>an AddressBook table.
>This sp expect a parameters called @.Address.
>When i call this sp and pass to it an email address which contains an at
>character (@.) the stored procedure will return no values.
>i think the @. alter the parameters value.
>Whitout the @. char the sp works fine .
>Can someone help me?
>Thanks|||How does your routine differ from the following?
create table address_book
(
address varchar(64)
)
insert address_book values ('address@.google.com')
create procedure add_check
@.address varchar(64)
as
if exists (select 1 from address_book where address = @.address)
print 'exists'
else
print 'not exists'
declare @.address varchar(64)
set @.address = 'address@.google.com'
exec add_check @.address|||Thanks. I solved. The error was that the parameter i declared was nvarchar
without the size.
When i changed it whit the same size of the table column, nvarchar(70), the
stored procedure works well and return me the right result.
Thanks to all.

email address domain strip of query

Could this query be made more efficient?
It takes ages to run it. It strips of everything from an email address and keeps only the domain.

SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, 100)) - 1) AS mail_domaingot 2005? string manipulation is faster in CLR.|||unfortunately no i don't. I may upgrade soon but for now it is 2000 im using.|||I assume that if you take this column out of the select list, the plan remains the same, but the query time goes up? Does the query time remain the same when you add the raw email address to the select list (and comment out all the substringing)? I am wondering, if you accidentally got a covering index on all of the fields not associated with the email addresses, or maybe the query is using different indexes for the query with all the substrings.

Also, not to be a nit picker, but which part of the domain are you after? An email address can be very complex. My brother has an email address that ends in @.haystack.mit.edu. Some of our British posters would have addresses like @.company.co.uk. I am guessing you would be interested in the "mit" or the "company", but not in "co' or "haystack".|||yes its from select list and no joins so it should be very fast. I se now that i have to make this to work with thoose split domain names aswell... damn :)

My question about the query was more kinda of " is this a stupid way to do this"?
i can live with the time it takes to run this query it is after al quite a large table.

thx for your help

Email address disappears in subscription

When I am in a subscription for a report in RS (SP1 installed) and I click on
Select Schedule, and then click on OK, any email addresses that I had in the
subscription are gone. Any addresses that I have in the Cc: or Bcc: are still
there but the To: list is empty.
Is this normal?No, this is not normal. Does the subscription work normally if you enter
the to field after setting the subscription? I am not really sure what
would cause some of the fields to stay but others not. Does this happen for
every user?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"garyc" <garyc@.discussions.microsoft.com> wrote in message
news:B1716E77-31E6-469E-9A41-35E1871975E6@.microsoft.com...
> When I am in a subscription for a report in RS (SP1 installed) and I click
on
> Select Schedule, and then click on OK, any email addresses that I had in
the
> subscription are gone. Any addresses that I have in the Cc: or Bcc: are
still
> there but the To: list is empty.
> Is this normal?|||In answer to your first question yes, the To: name does stay after you retype
it.
And yes this does happen for every user.
Gary
"Daniel Reib [MSFT]" wrote:
> No, this is not normal. Does the subscription work normally if you enter
> the to field after setting the subscription? I am not really sure what
> would cause some of the fields to stay but others not. Does this happen for
> every user?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "garyc" <garyc@.discussions.microsoft.com> wrote in message
> news:B1716E77-31E6-469E-9A41-35E1871975E6@.microsoft.com...
> > When I am in a subscription for a report in RS (SP1 installed) and I click
> on
> > Select Schedule, and then click on OK, any email addresses that I had in
> the
> > subscription are gone. Any addresses that I have in the Cc: or Bcc: are
> still
> > there but the To: list is empty.
> >
> > Is this normal?
>
>|||I'm not sure what would cause this behavior. My only guess would be that
perhaps you have an IE extension that is clearing it out for you (something
like the google toolbar). The fact that the other elements are working
correctly seems to indicate the issue is outside of RS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"garyc" <garyc@.discussions.microsoft.com> wrote in message
news:04FD1470-A465-4441-AA44-517163231D6B@.microsoft.com...
> In answer to your first question yes, the To: name does stay after you
retype
> it.
> And yes this does happen for every user.
> Gary
>
> "Daniel Reib [MSFT]" wrote:
> > No, this is not normal. Does the subscription work normally if you
enter
> > the to field after setting the subscription? I am not really sure what
> > would cause some of the fields to stay but others not. Does this happen
for
> > every user?
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "garyc" <garyc@.discussions.microsoft.com> wrote in message
> > news:B1716E77-31E6-469E-9A41-35E1871975E6@.microsoft.com...
> > > When I am in a subscription for a report in RS (SP1 installed) and I
click
> > on
> > > Select Schedule, and then click on OK, any email addresses that I had
in
> > the
> > > subscription are gone. Any addresses that I have in the Cc: or Bcc:
are
> > still
> > > there but the To: list is empty.
> > >
> > > Is this normal?
> >
> >
> >|||Daniel,
FWIW, I am experiencing the same issue as Gary when creating a subscription.
The To field gets cleared out every time I click the Select Schedule
button - all others remain. It happens consistently across browsers and
client machines. Not a show-stopper, but pretty annoying to say the least.
<g>
I have the following setting in RSReportServer.config:
<SendEmailToUserAlias>False</SendEmailToUserAlias>
When this is True, the field does not seem to get cleared out (maybe because
it's disabled?).
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
Daniel Reib [MSFT] wrote:
> I'm not sure what would cause this behavior. My only guess would be
> that perhaps you have an IE extension that is clearing it out for you
> (something like the google toolbar). The fact that the other
> elements are working correctly seems to indicate the issue is outside
> of RS.
>
> "garyc" <garyc@.discussions.microsoft.com> wrote in message
> news:04FD1470-A465-4441-AA44-517163231D6B@.microsoft.com...
>> In answer to your first question yes, the To: name does stay after
>> you retype it.
>> And yes this does happen for every user.
>> Gary
>>
>> "Daniel Reib [MSFT]" wrote:
>> No, this is not normal. Does the subscription work normally if you
>> enter the to field after setting the subscription? I am not really
>> sure what would cause some of the fields to stay but others not.
>> Does this happen for every user?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "garyc" <garyc@.discussions.microsoft.com> wrote in message
>> news:B1716E77-31E6-469E-9A41-35E1871975E6@.microsoft.com...
>> When I am in a subscription for a report in RS (SP1 installed) and
>> I click on Select Schedule, and then click on OK, any email
>> addresses that I had in the subscription are gone. Any addresses
>> that I have in the Cc: or Bcc: are still there but the To: list is
>> empty.
>> Is this normal?|||Ah ha. Looks like that is the key element. I have been able to reproduce
the problem and it is a bug. I will file it so that it can get fixed.
Thanks for helping us out.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jake Marx" <msnews@.longhead.com> wrote in message
news:ulKvLeIsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Daniel,
> FWIW, I am experiencing the same issue as Gary when creating a
subscription.
> The To field gets cleared out every time I click the Select Schedule
> button - all others remain. It happens consistently across browsers and
> client machines. Not a show-stopper, but pretty annoying to say the
least.
> <g>
> I have the following setting in RSReportServer.config:
> <SendEmailToUserAlias>False</SendEmailToUserAlias>
> When this is True, the field does not seem to get cleared out (maybe
because
> it's disabled?).
> --
> Regards,
> Jake Marx
> MS MVP - Excel
> www.longhead.com
> [please keep replies in the newsgroup - email address unmonitored]
>
> Daniel Reib [MSFT] wrote:
> > I'm not sure what would cause this behavior. My only guess would be
> > that perhaps you have an IE extension that is clearing it out for you
> > (something like the google toolbar). The fact that the other
> > elements are working correctly seems to indicate the issue is outside
> > of RS.
> >
> >
> > "garyc" <garyc@.discussions.microsoft.com> wrote in message
> > news:04FD1470-A465-4441-AA44-517163231D6B@.microsoft.com...
> >> In answer to your first question yes, the To: name does stay after
> >> you retype it.
> >>
> >> And yes this does happen for every user.
> >>
> >> Gary
> >>
> >>
> >> "Daniel Reib [MSFT]" wrote:
> >>
> >> No, this is not normal. Does the subscription work normally if you
> >> enter the to field after setting the subscription? I am not really
> >> sure what would cause some of the fields to stay but others not.
> >> Does this happen for every user?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "garyc" <garyc@.discussions.microsoft.com> wrote in message
> >> news:B1716E77-31E6-469E-9A41-35E1871975E6@.microsoft.com...
> >> When I am in a subscription for a report in RS (SP1 installed) and
> >> I click on Select Schedule, and then click on OK, any email
> >> addresses that I had in the subscription are gone. Any addresses
> >> that I have in the Cc: or Bcc: are still there but the To: list is
> >> empty.
> >>
> >> Is this normal?
>|||Have you gotten any solution? I got the same problem.|||I have the same issue. Have you gotten any solutions?



Thanks.

email

I would like to automate a report to be emailed to an email address.
When viewing the report, clicked on new subscription.
In the subscription window what options should there be for Delievered By: ?
I currently have: Report Server File Share in the drop down. Should there be other entries in the list too?
ThanksChange to Report Server Email and make sure that the report server is configured for Email Delivery.

email

I would like to automate a report to be emailed to an email address.
When viewing the report, clicked on new subscription.
In the subscription window what options should there be for Delievered By: ?
I currently have: Report Server File Share in the drop down. Should there be
other entries in the list too?
Thanksyes , you should have 'report serve email ' option too- if you dont, its
because you didnt choose the option at installation time to install SMTP
service.
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:91C6988E-808B-4049-AFA1-3B7A793F0220@.microsoft.com...
>I would like to automate a report to be emailed to an email address.
> When viewing the report, clicked on new subscription.
> In the subscription window what options should there be for Delievered By:
> ?
> I currently have: Report Server File Share in the drop down. Should there
> be
> other entries in the list too?
> Thanks|||How can I add this option whithout affecting the present instal/settings
please?
"Immy" wrote:
> yes , you should have 'report serve email ' option too- if you dont, its
> because you didnt choose the option at installation time to install SMTP
> service.
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:91C6988E-808B-4049-AFA1-3B7A793F0220@.microsoft.com...
> >I would like to automate a report to be emailed to an email address.
> > When viewing the report, clicked on new subscription.
> > In the subscription window what options should there be for Delievered By:
> > ?
> > I currently have: Report Server File Share in the drop down. Should there
> > be
> > other entries in the list too?
> > Thanks
>
>|||Hello again,
I do not remember seeing the SMTP option during the install.
Please note I am using SSRS 2005. The email we use is loyus notes.
Any thoughts pls?
Thanks
"Immy" wrote:
> yes , you should have 'report serve email ' option too- if you dont, its
> because you didnt choose the option at installation time to install SMTP
> service.
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:91C6988E-808B-4049-AFA1-3B7A793F0220@.microsoft.com...
> >I would like to automate a report to be emailed to an email address.
> > When viewing the report, clicked on new subscription.
> > In the subscription window what options should there be for Delievered By:
> > ?
> > I currently have: Report Server File Share in the drop down. Should there
> > be
> > other entries in the list too?
> > Thanks
>
>|||OK farshad, in RS2000 you have the option at installation time. For 2005 you
need to use the configuration tool.
See link below for steps:
http://msdn2.microsoft.com/en-us/library/ms159155.aspx
Immy
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:D25E292B-6886-4B0D-AD51-A518AF50BCA8@.microsoft.com...
> Hello again,
> I do not remember seeing the SMTP option during the install.
> Please note I am using SSRS 2005. The email we use is loyus notes.
> Any thoughts pls?
> Thanks
> "Immy" wrote:
>> yes , you should have 'report serve email ' option too- if you dont, its
>> because you didnt choose the option at installation time to install SMTP
>> service.
>> "farshad" <farshad@.discussions.microsoft.com> wrote in message
>> news:91C6988E-808B-4049-AFA1-3B7A793F0220@.microsoft.com...
>> >I would like to automate a report to be emailed to an email address.
>> > When viewing the report, clicked on new subscription.
>> > In the subscription window what options should there be for Delievered
>> > By:
>> > ?
>> > I currently have: Report Server File Share in the drop down. Should
>> > there
>> > be
>> > other entries in the list too?
>> > Thanks
>>

Friday, February 24, 2012

Eliminate Rows with Redundant Columns

I would like my query to return the KeyID from row 4 but from only one of
the first 3 rows where the address data is redundant. Since use of the key
eliminates the DISTINCT operator, is there another method?
DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
ST varchar(2))
INSERT @.tAddress
SELECT 1, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 2, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 3, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 4, '200 Main', 'Boston', 'MA'
Thanks!SELECT Address, City, ST, MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Or, if you really only wanted the ID column:
SELECT MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
<MikeH@.Cybervillage.net> wrote:

>I would like my query to return the KeyID from row 4 but from only one of
>the first 3 rows where the address data is redundant. Since use of the key
>eliminates the DISTINCT operator, is there another method?
>DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
>ST varchar(2))
>INSERT @.tAddress
>SELECT 1, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 2, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 3, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 4, '200 Main', 'Boston', 'MA'
>Thanks!
>|||Simple and elegant; I should have seen that. Many thanks Roy!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:csf092h8em6sabqjre5jf7jsbmrhroovtc@.
4ax.com...
> SELECT Address, City, ST, MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Or, if you really only wanted the ID column:
> SELECT MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
> <MikeH@.Cybervillage.net> wrote:
>

Wednesday, February 15, 2012

Editing User Defined Data type

Is there an easy way to edit an existing User Defined Data type ? For instance i have a custom data type address of base type nvarchar with a length of 40, I want to increase this custom data types length to 50, is there a way to do it or is it even supported?

The SQL Server 2005 Mangement Studio does not allow you do it..is there a work around for this?

Thanks,

Krishna

There is no easy way to do this without dropping and recreating the user-defined data type. You can do this via TSQL. I do not know about the GUI. You might want to ask this question in the SQL Server Tools forum.|||

Thanks, its the same with GUI, its not editable, it just allows us to either delete them and re-create them. Also before deleting them it makes us to remove all references, so its a bit of work..

Regards,

Krishna