Embedded tab Character

I have embedded tabs in a text field that I want to import to a destination table.

I was thinking I need to replace the tabs with spaces.

REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,LarryReplace ( fieldname, CHAR(10), ' ')|||

Thanks,

I beleive it is a char 9 , but I really wanted to change crlf Char(10) & CHAR(13).

Can I do this in one statement with the replace

Replace ( fieldname, CHAR(10) & CHAR(13), ' ')

Thanks,

Larry

|||

Regular expression work well to match and remove / replace string patterns.

To remove carriage return / new line combos, use a script component transform which replaces each occurrence of the pattern with an empty string.

To do so, drop a script component on the data flow canvas, selecting the desired column from the available input columns, and setting its usage type to Read/Write.

A regular expression is then used to match and remove the pattern.

Imports System
Imports

System.Data
Imports

System.Math
Imports

Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports

Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports

System.Text.RegularExpressions

Public Class ScriptMain
Inherits

UserComponent

Private

regex As Regex = New

Regex("\r\n",

RegexOptions.Compiled)

Public Overrides Sub

Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not Row.GeneratedStr1_IsNull Then
Row.GeneratedStr1 =

regex.Replace(Row.GeneratedStr1, String.Empty)
' Replace

all occurrences of pattern with empty string,
'provided input column is not null
End If
End Sub
End Class

|||

This is pretty Sweet, endless opportunity, unlimited potential.

Thanks,

Larry

|||

This is a SSIS forum, so how about a SSIS solution using REPLACE, may be easier than the Script Component route-

REPLACE(ColumnName, "\t", "")

\t is the escape sequenece for tab in a literal. This expression could be used in a Derived Column transform. Select the Replace "Column" option to clean existing columns in-place.

|||

Many ways to skin the Cat.

Can you do a crlf replace and a lf replace in the same replace statement.

There may be many line feeds and one crlf in the same text field that I am trying to clean.

Thanks,

Larry

|||

Nest the replace statements -

REPLACE(REPLACE(ColumenName, "\r\n", ""), "\n", "")

Or

REPLACE(REPLACE(ColumeName, CHAR(13) + CHAR(10), '', CHAR(10), '')

You could of course do just replace Cr and then Lf in a similar format, I just like the explicit nature of doing CrLf and Lf as units.

Email subscription. Dynamic field of From.

hi,
I have a console application calling reporting service to generate email
subscription. It works but the email from field always show the fix address
in RSReportServer.config. Is there a way I can programmable change the From
field with my code?
Please suggest.
regards,
JoeNo, the from field can not be changed. You can change the Reply-To field.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:50E4BCAD-DD67-494D-8D63-205D8E257BBD@.microsoft.com...
> hi,
> I have a console application calling reporting service to generate email
> subscription. It works but the email from field always show the fix
> address
> in RSReportServer.config. Is there a way I can programmable change the
> From
> field with my code?
> Please suggest.
> regards,
> Joe|||That is correct, the from field cannot be changed programmatically per
request. You can usually use the reply-to header to achieve what you're
looking for.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:50E4BCAD-DD67-494D-8D63-205D8E257BBD@.microsoft.com...
> hi,
> I have a console application calling reporting service to generate email
> subscription. It works but the email from field always show the fix
> address
> in RSReportServer.config. Is there a way I can programmable change the
> From
> field with my code?
> Please suggest.
> regards,
> Joe|||Thanks for your reply.
Joe
"Lukasz Pawlowski [MSFT]" wrote:
> That is correct, the from field cannot be changed programmatically per
> request. You can usually use the reply-to header to achieve what you're
> looking for.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:50E4BCAD-DD67-494D-8D63-205D8E257BBD@.microsoft.com...
> > hi,
> > I have a console application calling reporting service to generate email
> > subscription. It works but the email from field always show the fix
> > address
> > in RSReportServer.config. Is there a way I can programmable change the
> > From
> > field with my code?
> > Please suggest.
> > regards,
> > Joe
>
>sql

Email Subscription Problem

Regards,
When a non administrative person sets up an email subscription to a
particular report, the "to" field defaults to their login name rather than
their email address.
So for jsmith@.microsoft.com, the "to" field defaults to "jsmith" and is read
only so it can't be changed.
Once the subscription is made, the email goes to jsmith, which doesn't
resolve to anything causing a bounce-back.
Is there a setting to tell Reporting Services to use the AD email address
for users if available? Or is there a way to default the domain for missing
domains?
There has to be a setting in one of the config files.
Any help would be greatly appreciated!
Benjamin PierceIf all you need is to add host name to the email, there is a DefaultHostName
element in the RSReportServer config file. This will add the host name to
any email address that does not have one.
If you want all users to be able to edit the TO line set the
SendEmailToUserAlias element to false and then every user will have a blank
TO line that they can set.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:OmZZyvLTFHA.3264@.TK2MSFTNGP15.phx.gbl...
> Regards,
> When a non administrative person sets up an email subscription to a
> particular report, the "to" field defaults to their login name rather than
> their email address.
> So for jsmith@.microsoft.com, the "to" field defaults to "jsmith" and is
> read
> only so it can't be changed.
> Once the subscription is made, the email goes to jsmith, which doesn't
> resolve to anything causing a bounce-back.
> Is there a setting to tell Reporting Services to use the AD email address
> for users if available? Or is there a way to default the domain for
> missing
> domains?
> There has to be a setting in one of the config files.
> Any help would be greatly appreciated!
> Benjamin Pierce
>|||Thanks Daniel,
Adding a DefaultHostName worked like a charm. VERY MUCH APPRECIATED!
Benjamin Pierce
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:O9fs3QQTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> If all you need is to add host name to the email, there is a
DefaultHostName
> element in the RSReportServer config file. This will add the host name to
> any email address that does not have one.
> If you want all users to be able to edit the TO line set the
> SendEmailToUserAlias element to false and then every user will have a
blank
> TO line that they can set.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:OmZZyvLTFHA.3264@.TK2MSFTNGP15.phx.gbl...
> > Regards,
> >
> > When a non administrative person sets up an email subscription to a
> > particular report, the "to" field defaults to their login name rather
than
> > their email address.
> >
> > So for jsmith@.microsoft.com, the "to" field defaults to "jsmith" and is
> > read
> > only so it can't be changed.
> >
> > Once the subscription is made, the email goes to jsmith, which doesn't
> > resolve to anything causing a bounce-back.
> >
> > Is there a setting to tell Reporting Services to use the AD email
address
> > for users if available? Or is there a way to default the domain for
> > missing
> > domains?
> >
> > There has to be a setting in one of the config files.
> >
> > Any help would be greatly appreciated!
> >
> > Benjamin Pierce
> >
> >
>

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

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

Email ID Validation in SQL Query

Hi,
Can someone help me with a query to validate if a value stored in the
emailaddress field in a table is a valid email id or no. Please mail me
ASAP. Thanks in advance.
Regards
DineshDinesh
I'm not sure iunderstood your question.
Since you have not provided a table structure along with sample data , I
guess you can do something like that
IF EXISTS (SELECT * FROM Table WHERE email_id=@.par)
--do soemthing
ELSE
--do somethimg else
"Dinesh" <Dinesh@.discussions.microsoft.com> wrote in message
news:80CE816E-A91C-4ECE-93E4-19006167B567@.microsoft.com...
> Hi,
> Can someone help me with a query to validate if a value stored in the
> emailaddress field in a table is a valid email id or no. Please mail me
> ASAP. Thanks in advance.
> Regards
> Dinesh|||Dinesh skrev:

> Hi,
> Can someone help me with a query to validate if a value stored in the
> emailaddress field in a table is a valid email id or no. Please mail me
> ASAP. Thanks in advance.
> Regards
> Dinesh
Depends on what you actually want to validate...
Is it only that the value looks like 'somename@.someaddress', or is it
to check if there really IS such an (active?) email account, or is it
to check against a table with valid email addresses? Or something else?
/impslayer, aka Birger Johansson|||Please explain in detail, your question? Mean provide sample data situation
Thanks,
Siva
"impslayer" wrote:

> Dinesh skrev:
>
> Depends on what you actually want to validate...
> Is it only that the value looks like 'somename@.someaddress', or is it
> to check if there really IS such an (active?) email account, or is it
> to check against a table with valid email addresses? Or something else?
> /impslayer, aka Birger Johansson
>

email Hyperlink

Hi! I have a report that is showing an email account.
I wanted to let the user click on this field, so, if he/she has an
email program set up, they can email directly.
Do you know how can I do it?
Thanks!!Yeah. I think you'll want to use an "action".
Ryan
Smelly wrote:
> Hi! I have a report that is showing an email account.
> I wanted to let the user click on this field, so, if he/she has an
> email program set up, they can email directly.
> Do you know how can I do it?
> Thanks!!|||Properties/Navigation/Jump to URL:
mailto:name@.company.com
"Smelly" wrote:
> Hi! I have a report that is showing an email account.
> I wanted to let the user click on this field, so, if he/she has an
> email program set up, they can email directly.
> Do you know how can I do it?
> Thanks!!
>

E-mail delivery configuration

I have looked high and low - everywhere - but can't seem
to find the answer.
Books on-line talks about the SMTPAuthenticate field in
the RSReportServer.config file and indicates that the
fields sendusername and senduserpassword need to be set
if authentication to the smtp server is required (in my
case it is).
Everytime I set the config file with the options that
seem to be correct from Books on-line, the email delivery
either fails or the e-mail delivery option on the
subscription is not available - usually meaning problems
with reportServer reading the config file.
Has anyone set up SMTP authentication with report server.
Below are pertinent data related to this problem.
notes from Books on-line --
SMTPAuthenticate Specifies an integer value that
indicates the kind of authentication to use when sending
messages to an SMTP service over a TCP/IP connection.
0=no authentication.
1=basic authentication. Credentials are passed in clear
text using either sendusername and senduserpassword, or
postusername and postuserpassword fields.
2= NTML (NT LanMan) authentication. The security context
of the current process is used to authenticate the
service.
It appears the sendusername and senduserpassword are from
CDO objects but when you look in the CDO Docs on MSDN it
identifies sendusername and sendpassword fields.
I have tried to set the following in the config file...
<SendUserName>wc.smith@.sbcglobal.net</SendUserName>
<SendPassword>7776787</SendPassword> (bogus password here)
and although ReportServer does not seem to error on the
config file the e-mail option on the subscription is not
available. It only has the fileshare option.
I am logged into Report Manager as admin.
Here is the snippet from the config file...
<Extensions>
<Delivery>
<Extension Name="Report Server
FileShare"
Type="Microsoft.ReportingServices.FileShareDeliveryProvide
r.FileShareProvider,ReportingServicesFileShareDeliveryProv
ider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<FileShareConfiguration>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
</FileShareConfiguration>
</Configuration>
</Extension>
<Extension Name="Report Server
Email"
Type="Microsoft.ReportingServices.EmailDeliveryProvider.Em
ailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName>WarrenSmith-sbc</SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirec
tory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate>1</SMTPAuthenticate>
<From>wc.smith@.sbcglobal.net</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRende
rFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.NullDeliveryProvider.Nul
lProvider,ReportingServicesNullDeliveryProvider"/>
Does anyone know where you set the SendUserName and
SendPassword fields?
Thanks,
WarrenAs an update to the problem. If I put a 1 in the
SMTPAuthenticate field the config file errors off. I can
leave it blank and then my smtp server complains that I
am not authenticating. I can put a 2 in the field and
then report services does not deliver the email because I
am not using winnt authentication.
I have also tried to put in the SendUserName and
SendPassword (spelled this way and all lowercase, as in
the DOCS) as attributes...
<SMTPAuthenticate SendUserName="wc.smith@.sbcglobal.net"
SendPassword="3333333"></SMTPAuthenticate>
That does not error the config file unless, again you put
a 1 for the value of the element. Again a blank for the
SMTPAuthenticate element causes report services to fail
with a non-authentication (503) from the smtp server...
Here is the error I get for the subscription...
Failure sending mail: The server rejected the sender
address. The server response was: 530 authentication
required - for help go to
http://help.yahoo.com/help/us/sbc/dsl/mail/pop/pop-
11.html
Here is the delivery extensions area of the
RSReportServer.config file...
<Extension Name="Report Server
Email"
Type="Microsoft.ReportingServices.EmailDeliveryProvider.Em
ailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName>WarrenSmith</SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirec
tory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>wc.smith@.sbcglobal.net</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRende
rFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName>sbcglobal.net</DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>
>--Original Message--
>I have looked high and low - everywhere - but can't seem
>to find the answer.
>Books on-line talks about the SMTPAuthenticate field in
>the RSReportServer.config file and indicates that the
>fields sendusername and senduserpassword need to be set
>if authentication to the smtp server is required (in my
>case it is).
>Everytime I set the config file with the options that
>seem to be correct from Books on-line, the email
delivery
>either fails or the e-mail delivery option on the
>subscription is not available - usually meaning problems
>with reportServer reading the config file.
>Has anyone set up SMTP authentication with report server.
>Below are pertinent data related to this problem.
>notes from Books on-line --
>SMTPAuthenticate Specifies an integer value that
>indicates the kind of authentication to use when sending
>messages to an SMTP service over a TCP/IP connection.
>0=no authentication.
>1=basic authentication. Credentials are passed in clear
>text using either sendusername and senduserpassword, or
>postusername and postuserpassword fields.
>2= NTML (NT LanMan) authentication. The security context
>of the current process is used to authenticate the
>service.
>It appears the sendusername and senduserpassword are
from
>CDO objects but when you look in the CDO Docs on MSDN it
>identifies sendusername and sendpassword fields.
>I have tried to set the following in the config file...
><SendUserName>wc.smith@.sbcglobal.net</SendUserName>
><SendPassword>7776787</SendPassword> (bogus password
here)
>and although ReportServer does not seem to error on the
>config file the e-mail option on the subscription is not
>available. It only has the fileshare option.
>I am logged into Report Manager as admin.
>Here is the snippet from the config file...
> <Extensions>
> <Delivery>
> <Extension Name="Report Server
>FileShare"
>Type="Microsoft.ReportingServices.FileShareDeliveryProvid
e
>r.FileShareProvider,ReportingServicesFileShareDeliveryPro
v
>ider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <FileShareConfiguration>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> </FileShareConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="Report Server
>Email"
>Type="Microsoft.ReportingServices.EmailDeliveryProvider.E
m
>ailProvider,ReportingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
> <SMTPServerPort>25</SMTPServerPort>
> <SMTPAccountName>WarrenSmith-sbc</SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirec
>tory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing>2</SendUsing>
> <SMTPAuthenticate>1</SMTPAuthenticate>
> <From>wc.smith@.sbcglobal.net</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRende
>rFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName></DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="NULL"
>Type="Microsoft.ReportingServices.NullDeliveryProvider.Nu
l
>lProvider,ReportingServicesNullDeliveryProvider"/>
>
>Does anyone know where you set the SendUserName and
>SendPassword fields?
>Thanks,
>Warren
>
>.
>|||Unfortunately, SendUserName and SendPassword are not supported in the e-mail
delivery extension. The only value that works other than 0 is 2 (which uses
the service account credentials).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:2fb0d01c46d34$380d4fe0$a301280a@.phx.gbl...
> As an update to the problem. If I put a 1 in the
> SMTPAuthenticate field the config file errors off. I can
> leave it blank and then my smtp server complains that I
> am not authenticating. I can put a 2 in the field and
> then report services does not deliver the email because I
> am not using winnt authentication.
> I have also tried to put in the SendUserName and
> SendPassword (spelled this way and all lowercase, as in
> the DOCS) as attributes...
> <SMTPAuthenticate SendUserName="wc.smith@.sbcglobal.net"
> SendPassword="3333333"></SMTPAuthenticate>
> That does not error the config file unless, again you put
> a 1 for the value of the element. Again a blank for the
> SMTPAuthenticate element causes report services to fail
> with a non-authentication (503) from the smtp server...
> Here is the error I get for the subscription...
> Failure sending mail: The server rejected the sender
> address. The server response was: 530 authentication
> required - for help go to
> http://help.yahoo.com/help/us/sbc/dsl/mail/pop/pop-
> 11.html
>
> Here is the delivery extensions area of the
> RSReportServer.config file...
> <Extension Name="Report Server
> Email"
> Type="Microsoft.ReportingServices.EmailDeliveryProvider.Em
> ailProvider,ReportingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
> <SMTPServerPort>25</SMTPServerPort>
> <SMTPAccountName>WarrenSmith</SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirec
> tory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing>2</SendUsing>
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>wc.smith@.sbcglobal.net</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRende
> rFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName>sbcglobal.net</DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> </Extension>
>
>>--Original Message--
>>I have looked high and low - everywhere - but can't seem
>>to find the answer.
>>Books on-line talks about the SMTPAuthenticate field in
>>the RSReportServer.config file and indicates that the
>>fields sendusername and senduserpassword need to be set
>>if authentication to the smtp server is required (in my
>>case it is).
>>Everytime I set the config file with the options that
>>seem to be correct from Books on-line, the email
> delivery
>>either fails or the e-mail delivery option on the
>>subscription is not available - usually meaning problems
>>with reportServer reading the config file.
>>Has anyone set up SMTP authentication with report server.
>>Below are pertinent data related to this problem.
>>notes from Books on-line --
>>SMTPAuthenticate Specifies an integer value that
>>indicates the kind of authentication to use when sending
>>messages to an SMTP service over a TCP/IP connection.
>>0=no authentication.
>>1=basic authentication. Credentials are passed in clear
>>text using either sendusername and senduserpassword, or
>>postusername and postuserpassword fields.
>>2= NTML (NT LanMan) authentication. The security context
>>of the current process is used to authenticate the
>>service.
>>It appears the sendusername and senduserpassword are
> from
>>CDO objects but when you look in the CDO Docs on MSDN it
>>identifies sendusername and sendpassword fields.
>>I have tried to set the following in the config file...
>><SendUserName>wc.smith@.sbcglobal.net</SendUserName>
>><SendPassword>7776787</SendPassword> (bogus password
> here)
>>and although ReportServer does not seem to error on the
>>config file the e-mail option on the subscription is not
>>available. It only has the fileshare option.
>>I am logged into Report Manager as admin.
>>Here is the snippet from the config file...
>> <Extensions>
>> <Delivery>
>> <Extension Name="Report Server
>>FileShare"
>>Type="Microsoft.ReportingServices.FileShareDeliveryProvid
> e
>>r.FileShareProvider,ReportingServicesFileShareDeliveryPro
> v
>>ider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <FileShareConfiguration>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> </FileShareConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="Report Server
>>Email"
>>Type="Microsoft.ReportingServices.EmailDeliveryProvider.E
> m
>>ailProvider,ReportingServicesEmailDeliveryProvider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <RSEmailDPConfiguration>
>> <SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
>> <SMTPServerPort>25</SMTPServerPort>
>> <SMTPAccountName>WarrenSmith-sbc</SMTPAccountName>
>> <SMTPConnectionTimeout></SMTPConnectionTimeout>
>> <SMTPServerPickupDirectory></SMTPServerPickupDirec
>>tory>
>> <SMTPUseSSL></SMTPUseSSL>
>> <SendUsing>2</SendUsing>
>> <SMTPAuthenticate>1</SMTPAuthenticate>
>> <From>wc.smith@.sbcglobal.net</From>
>> <EmbeddedRenderFormats>
>> <RenderingExtension>MHTML</RenderingExtension>
>> </EmbeddedRenderFormats>
>> <PrivilegedUserRenderFormats></PrivilegedUserRende
>>rFormats>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> <SendEmailToUserAlias>True</SendEmailToUserAlias>
>> <DefaultHostName></DefaultHostName>
>> <PermittedHosts></PermittedHosts>
>> </RSEmailDPConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="NULL"
>>Type="Microsoft.ReportingServices.NullDeliveryProvider.Nu
> l
>>lProvider,ReportingServicesNullDeliveryProvider"/>
>>
>>Does anyone know where you set the SendUserName and
>>SendPassword fields?
>>Thanks,
>>Warren
>>
>>.|||I have found that I had to exempt my RS machine's ip from SMTP Auth to get this to go.
"Brian Welcker [MSFT]" wrote:
> Unfortunately, SendUserName and SendPassword are not supported in the e-mail
> delivery extension. The only value that works other than 0 is 2 (which uses
> the service account credentials).
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2fb0d01c46d34$380d4fe0$a301280a@.phx.gbl...
> > As an update to the problem. If I put a 1 in the
> > SMTPAuthenticate field the config file errors off. I can
> > leave it blank and then my smtp server complains that I
> > am not authenticating. I can put a 2 in the field and
> > then report services does not deliver the email because I
> > am not using winnt authentication.
> >
> > I have also tried to put in the SendUserName and
> > SendPassword (spelled this way and all lowercase, as in
> > the DOCS) as attributes...
> > <SMTPAuthenticate SendUserName="wc.smith@.sbcglobal.net"
> > SendPassword="3333333"></SMTPAuthenticate>
> >
> > That does not error the config file unless, again you put
> > a 1 for the value of the element. Again a blank for the
> > SMTPAuthenticate element causes report services to fail
> > with a non-authentication (503) from the smtp server...
> >
> > Here is the error I get for the subscription...
> >
> > Failure sending mail: The server rejected the sender
> > address. The server response was: 530 authentication
> > required - for help go to
> > http://help.yahoo.com/help/us/sbc/dsl/mail/pop/pop-
> > 11.html
> >
> >
> > Here is the delivery extensions area of the
> > RSReportServer.config file...
> >
> > <Extension Name="Report Server
> > Email"
> > Type="Microsoft.ReportingServices.EmailDeliveryProvider.Em
> > ailProvider,ReportingServicesEmailDeliveryProvider">
> > <MaxRetries>3</MaxRetries>
> >
> > <SecondsBeforeRetry>900</SecondsBeforeRetry>
> > <Configuration>
> >
> > <RSEmailDPConfiguration>
> >
> > <SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
> >
> > <SMTPServerPort>25</SMTPServerPort>
> >
> > <SMTPAccountName>WarrenSmith</SMTPAccountName>
> >
> > <SMTPConnectionTimeout></SMTPConnectionTimeout>
> >
> > <SMTPServerPickupDirectory></SMTPServerPickupDirec
> > tory>
> >
> > <SMTPUseSSL></SMTPUseSSL>
> >
> > <SendUsing>2</SendUsing>
> >
> > <SMTPAuthenticate></SMTPAuthenticate>
> >
> > <From>wc.smith@.sbcglobal.net</From>
> >
> > <EmbeddedRenderFormats>
> >
> > <RenderingExtension>MHTML</RenderingExtension>
> >
> > </EmbeddedRenderFormats>
> >
> > <PrivilegedUserRenderFormats></PrivilegedUserRende
> > rFormats>
> >
> > <ExcludedRenderFormats>
> >
> > <RenderingExtension>HTMLOWC</RenderingExtension>
> >
> > <RenderingExtension>NULL</RenderingExtension>
> >
> > </ExcludedRenderFormats>
> >
> > <SendEmailToUserAlias>True</SendEmailToUserAlias>
> >
> > <DefaultHostName>sbcglobal.net</DefaultHostName>
> >
> > <PermittedHosts></PermittedHosts>
> >
> > </RSEmailDPConfiguration>
> > </Configuration>
> > </Extension>
> >
> >
> >
> >>--Original Message--
> >>I have looked high and low - everywhere - but can't seem
> >>to find the answer.
> >>
> >>Books on-line talks about the SMTPAuthenticate field in
> >>the RSReportServer.config file and indicates that the
> >>fields sendusername and senduserpassword need to be set
> >>if authentication to the smtp server is required (in my
> >>case it is).
> >>
> >>Everytime I set the config file with the options that
> >>seem to be correct from Books on-line, the email
> > delivery
> >>either fails or the e-mail delivery option on the
> >>subscription is not available - usually meaning problems
> >>with reportServer reading the config file.
> >>
> >>Has anyone set up SMTP authentication with report server.
> >>
> >>Below are pertinent data related to this problem.
> >>
> >>notes from Books on-line --
> >>SMTPAuthenticate Specifies an integer value that
> >>indicates the kind of authentication to use when sending
> >>messages to an SMTP service over a TCP/IP connection.
> >>0=no authentication.
> >>1=basic authentication. Credentials are passed in clear
> >>text using either sendusername and senduserpassword, or
> >>postusername and postuserpassword fields.
> >>
> >>2= NTML (NT LanMan) authentication. The security context
> >>of the current process is used to authenticate the
> >>service.
> >>
> >>It appears the sendusername and senduserpassword are
> > from
> >>CDO objects but when you look in the CDO Docs on MSDN it
> >>identifies sendusername and sendpassword fields.
> >>
> >>I have tried to set the following in the config file...
> >><SendUserName>wc.smith@.sbcglobal.net</SendUserName>
> >><SendPassword>7776787</SendPassword> (bogus password
> > here)
> >>and although ReportServer does not seem to error on the
> >>config file the e-mail option on the subscription is not
> >>available. It only has the fileshare option.
> >>
> >>I am logged into Report Manager as admin.
> >>
> >>Here is the snippet from the config file...
> >>
> >> <Extensions>
> >> <Delivery>
> >> <Extension Name="Report Server
> >>FileShare"
> >>Type="Microsoft.ReportingServices.FileShareDeliveryProvid
> > e
> >>r.FileShareProvider,ReportingServicesFileShareDeliveryPro
> > v
> >>ider">
> >> <MaxRetries>3</MaxRetries>
> >>
> >> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> >> <Configuration>
> >>
> >> <FileShareConfiguration>
> >>
> >> <ExcludedRenderFormats>
> >>
> >> <RenderingExtension>HTMLOWC</RenderingExtension>
> >>
> >> <RenderingExtension>NULL</RenderingExtension>
> >>
> >> </ExcludedRenderFormats>
> >>
> >> </FileShareConfiguration>
> >> </Configuration>
> >> </Extension>
> >> <Extension Name="Report Server
> >>Email"
> >>Type="Microsoft.ReportingServices.EmailDeliveryProvider.E
> > m
> >>ailProvider,ReportingServicesEmailDeliveryProvider">
> >> <MaxRetries>3</MaxRetries>
> >>
> >> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> >> <Configuration>
> >>
> >> <RSEmailDPConfiguration>
> >>
> >> <SMTPServer>smtp.sbcglobal.yahoo.com</SMTPServer>
> >>
> >> <SMTPServerPort>25</SMTPServerPort>
> >>
> >> <SMTPAccountName>WarrenSmith-sbc</SMTPAccountName>
> >>
> >> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> >>
> >> <SMTPServerPickupDirectory></SMTPServerPickupDirec
> >>tory>
> >>
> >> <SMTPUseSSL></SMTPUseSSL>
> >>
> >> <SendUsing>2</SendUsing>
> >>
> >> <SMTPAuthenticate>1</SMTPAuthenticate>
> >>
> >> <From>wc.smith@.sbcglobal.net</From>
> >>
> >> <EmbeddedRenderFormats>
> >>
> >> <RenderingExtension>MHTML</RenderingExtension>
> >>
> >> </EmbeddedRenderFormats>
> >>
> >> <PrivilegedUserRenderFormats></PrivilegedUserRende
> >>rFormats>
> >>
> >> <ExcludedRenderFormats>
> >>
> >> <RenderingExtension>HTMLOWC</RenderingExtension>
> >>
> >> <RenderingExtension>NULL</RenderingExtension>
> >>
> >> </ExcludedRenderFormats>
> >>
> >> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> >>
> >> <DefaultHostName></DefaultHostName>
> >>
> >> <PermittedHosts></PermittedHosts>
> >>
> >> </RSEmailDPConfiguration>
> >> </Configuration>
> >> </Extension>
> >> <Extension Name="NULL"
> >>Type="Microsoft.ReportingServices.NullDeliveryProvider.Nu
> > l
> >>lProvider,ReportingServicesNullDeliveryProvider"/>
> >>
> >>
> >>Does anyone know where you set the SendUserName and
> >>SendPassword fields?
> >>
> >>Thanks,
> >>
> >>Warren
> >>
> >>
> >>.
> >>
>
>

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table'
Thanks for answering.
Message posted via http://www.droptable.comHi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via droptable.com" wrote:

> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
>|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.SQ
droptable.com...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.droptable.com|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
Message posted via http://www.droptable.com|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via droptable.com" <forum@.droptable.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.SQ
droptable.com...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.droptable.com|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
Message posted via http://www.droptable.com

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table?
Thanks for answering.
Message posted via http://www.droptable.com
Hi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via droptable.com" wrote:

> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table?
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
>
|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.droptable.co m...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table?
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
Message posted via http://www.droptable.com
|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via droptable.com" <forum@.droptable.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.droptable.co m...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.droptable.com
|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
Message posted via http://www.droptable.com

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table'
Thanks for answering.
--
Message posted via http://www.sqlmonster.comHi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via SQLMonster.com" wrote:
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com
>|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.SQLMonster.com...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
--
Message posted via http://www.sqlmonster.com|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.SQLMonster.com...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.sqlmonster.com|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
--
Message posted via http://www.sqlmonster.com

EM and QA displays different format for a date field

Hi,
I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 But
Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
Table1)
So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
PC does the same.
Thanks.You have a problem under standing of what a data value is and how it is
stored. It is actually just stored as numeric value, denoting offsets from
some time in the reasonably distant past. Format is based on the user. QA
uses a standard value, mostly because when you are using QA the goal is
usually to see ALL of the data.
Enterprise manager is probably using your system settings. When you
actually use the data, you can format it as you wish.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OqYzX0yUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a datetime field with a value '1/1/02'. EM display it as 1/1/02
> But Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT *
> FROM Table1)
> So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
> PC does the same.
> Thanks.
>|||Chrissi,
Different clients display things differently, with the possible
excepting of strings made up of printable ASCII with codes
from 32 to 127.
If you want the datetime displayed in a particular format, I'd
suggest you select CONVERT(varchar(40),yourDatetimeColumn,X
XXX),
where XXXX is the appropriate SQL Server format code (see the
Books Online article CAST and CONVERT).
If you do this, you won't be able to "edit" the datetime values
in Enterprise Manager, but that's not a good idea anyway. Better
to make changes programmatically with UPDATE or INSERT
statements.
Steve Kass
Drew University
Chrissi wrote:

>Hi,
>I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 Bu
t
>Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
>Table1)
>So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
>PC does the same.
>Thanks.
>
>

Eliminating spaces in query output

Is there a way that I can do a select statement that will just select the text within a field. ie. If the field is 200 wide, but the actual text is only 20 characters, can I select JUST the 20 characters with nothing else? I need to do this for a huge number of rows ....

Also, I'm running the select statement via osql if that helps.When selecting the column name you can use the trim function, e.g.

select rtrim(ltrim(emp_name))
from employees

This will trim blank spaces from the left and right hand ends of the character string. If you only need to clear trailing blanks just use the rtrim function.|||RTRIM was just what I needed, thank you.

Eliminating Duplicates

I have a table with a field called PID. This field can have duplicate
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>

eliminating (inverse) duplicates in result

Am I going about this the right way? I want to find pairs of entities
in a table that have some relationship (such as a field being the
same), so I

select t1.id, t2.id from sametable t1 join sametable t2 on
t1.id<>t2.id
where t1.fieldx=t2.fieldx ...

The trouble is, this returns each pair twice, e.g.

B C
C B
M N
N M

Is there a way to do this kind of thing and only get each pair once?

KerryTry this:

SELECT t1.id, t2.id
FROM sametable t1
JOIN sametable t2
ON t1.id < t2.id
WHERE t1.fieldx=t2.fieldx ...

(a subtle difference in the ON clause)

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the tip. Very elegant. Makes the join half as big too!

Thanks,

Kerry

Eleminate trailing zeros in the numeric field

Hello,
I have a numeric field with 4 decimal places.
In the select query i want to eliminate trailing zeros.
DB Value Result
1.1234 1.1234
1.1230 1.123
1.1200 1.12
1.1000 1.1
1.0000 1
Thanks in advance.
M. SubbaiahWhat you want to do is formatting a string. In order to do that you need to
CAST the value to a character data type. Do you really want to do that?
Anyway, here's a nice article on casting and converting data in SQL:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
Look at the style parameter.
ML
http://milambda.blogspot.com/

Electrical Symbols - Ohms - micro

Hi,
would anyone be able to point me in the right direction on how to save
the
electrical symbols for ohms and micro in a sql server field.
Many thanks,Make sure you use NVARCHAR, and pass the character in a string with the N
prefix.
CREATE TABLE dbo.foo
(
symbol NVARCHAR(32)
);
GO
INSERT dbo.foo
SELECT N''; -- not sure if this will be correct in your newsreader
GO
SELECT symbol FROM dbo.foo;
GO
DROP TABLE foo;
GO
"Jedawi" <jedawi@.hotmail.co.uk> wrote in message
news:eK9ZVWkBGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> would anyone be able to point me in the right direction on how to save
> the
> electrical symbols for ohms and micro in a sql server field.
> Many thanks,
>
>|||Many thanks Aaron, works a treat..
Cheers
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVLElYkBGHA.1008@.TK2MSFTNGP12.phx.gbl...
> Make sure you use NVARCHAR, and pass the character in a string with the N
> prefix.
> CREATE TABLE dbo.foo
> (
> symbol NVARCHAR(32)
> );
> GO
> INSERT dbo.foo
> SELECT N''; -- not sure if this will be correct in your newsreader
> GO
> SELECT symbol FROM dbo.foo;
> GO
> DROP TABLE foo;
> GO
>
>
> "Jedawi" <jedawi@.hotmail.co.uk> wrote in message
> news:eK9ZVWkBGHA.1288@.TK2MSFTNGP09.phx.gbl...
>

efficiency of sql server on searching on text field

Hi

We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?

The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?

Please advise, thanks for your help!

Li

Id it a TEXT field or a field with characters stored in it ? How much data can be stored in the attribute ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually it is text field with 10 character length

|||

Well 10k rows is not that much. It could be that SQL Server will even cache the rows or do a table scan rather than touch the index. but in terms of planning for the future, you should consider using an index if the attribute is queried a lot.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||did u tried to create an index that match the search query

find below steps of choosing the most needed indexes

The following query will get the 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

You can get the missing index details in the following way:

The following query determines which missing indexes comprise a particular missing index group, and displays their column details.

For the sake of this example, the missing index group handle is 24.(You will need to change the handle value with handle values which comes up from the earlier query)

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats migs

INNER JOIN sys.dm_db_missing_index_groups mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24 <<put your handle value here>>

For details on this refer to the following articles:

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

Using Missing Index Information to Write CREATE INDEX Statements

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

Efficiency in inserting Null Values into fields which allow nulls.

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanksheres some info from BOL :

Allowing Null Values
The nullability of a column determines if the rows in the table can contain a null value for that column. A null value, or NULL, is not the same as zero (0), blank, or a zero-length character string such as ""; NULL means that no entry has been made. The presence NULL usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs database does not mean that the book has no price; NULL means that the price is unknown or has not been set.In general, avoid permitting null values because they incur more complexity in queries and updates and because there are other column options, such as PRIMARY KEY constraints, that cannot be used with nullable columns.

If a row is inserted but no value is included for a column that allows null values, Microsoft® SQL Server? 2000 supplies the value NULL (unless a DEFAULT definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it. The value NULL should not be placed within quotation marks because it will be interpreted as the character string 'NULL', rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

Note Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.
|||To be honest I'm not sure. Once you've made the decision to use NULLs (and there are lots of pros/cons to that) I doubt there is much in it. However, if you have a choice *and* you're really trying to eek out the last drop of performance then I'd avoid NULLs. Having said that I'm sure there are thousands of other places to look for better optimisations before you go here.