No field delimiters using bcp command

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

Quote:

Originally Posted by karafokas

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

looks like you want a fixed-length output. have you tried passing -t "" ? or something like that? -t is the bcp parameter for field terminator.sql

No Decimal trailing sign

I have a field defined as (decimal 9(15,2)) and the recipient of a conversion
..txt file wants to see just the meaningful digits, no decimals, no zero fill
but does want to
see a trailing minus sign for negative numbers. So they want to see 550.45
as 55045 and -45.25 as 4525-.
Stan Gosselin
Stan,
You'll have to use string functions to do this.
Here's one solution:
declare @.t table (
d decimal(15,2)
)
insert into @.t values (550.45)
insert into @.t values (10000)
insert into @.t values (-45.25)
insert into @.t values (0)
insert into @.t values (0.01)
insert into @.t values (1)
select
case when d >= 0
then ltrim(cast(100*d as int))
else ltrim(cast(-100*d as int)) + '-' end
from @.t
Be sure you and the client agree on how to represent everything.
This solution represents 0 as '0', not '000', for example, which may
or may not be right.
Steve Kass
Drew University
Stan wrote:

>I have a field defined as (decimal 9(15,2)) and the recipient of a conversion
>.txt file wants to see just the meaningful digits, no decimals, no zero fill
>but does want to
>see a trailing minus sign for negative numbers. So they want to see 550.45
>as 55045 and -45.25 as 4525-.
>
>
sql

No Date passed

I want to update a record that has a datetime field in it. But what if there is no parameter given for that field, i.e. what is the correct type/value to pass as parameter?

Example: A DOB field for a user profile, but the user doesn't enter his birthday

This is some example code that I use to update:

 Private Sub UpdateDOB(ByVal dob As Date)
 Dim parameters As SqlParameter() = { _
 New SqlParameter("@.Birthday", SqlDbType.DateTime, 8)}
 parameters(0).Value = dob
 'Run Stored Procedure

This gives me the exception "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when I don't pass a valid date. I tried passingNothing but same error (in this case it convert dob to "#12:00:00 AM#")

The only thing I can think of is to use Date.MaxValue and then check in application logic, but there must be a better way!use dbnull.value

hth|||ndinakar, I tried this:

User.DOB = System.DBNull.Value

but then I get an error, DBNull.Value cannot be converted to Type Date
how should i assign DBNull.Value ?|||Instead of passing DOB as date pass it as string

Private Sub UpdateDOB(ByVal strDOB As string)
...
cmd.Parameters.Add(New SqlParameter("@.Birthday", SqlDbType.DateTime))
If (strDOB= "") Then
cmd.Parameters("@.Date").Value = sqldatenull
'cmd.Parameters("@.Date").Value = DBNull.Value
Else
 cmd.Parameters("@.Date").Value = DateTime.Parse(strDOB)
End If

HTH|||or this :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
 myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
 myCommand.Parameters("@.cusbday").Value = server.htmlencode(DateTime.Parse(bday.Text))
End If

you need to import system.data.sqldypes namespace.

hth|||Thank you

SqlDateTime.Null worked fine as parameter.sql

no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
SarahUse the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>|||Are you using SQL Server 2000? If so, look up the COLLATE keyword in BOL.
Here's a very simple example:
create table #a(mychar varchar(20) collate sql_latin1_general_cp1_cs_as)
go
insert #a values('aBc')
insert #a values('abC')
go
select distinct mychar from #a
go
select distinct mychar collate sql_latin1_general_cp1_ci_as from #a
go
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>sql

no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
Sarah
Use the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>

No available values in Report Builder parameter

In a Report builder report I have runtime prompt filter on a field.
But I am not able to see the available values in the dropdown for this field.
My filter is on a "New Field" with the formula :
IF(LENGTH(FieldName)>0,FieldName,("Blank"))

However if my filter is on the FieldName directly and not on the New Field with the above formula I do get the available values.
Is there any way I can get the available values in a dropdown for the New Field?

In Model Designer, add the "New Field" formula as a new attribute to the entity. Then set ValueSelection of the attribute to be dropdown.|||

True any field which is present in the Model can be shown in the dropdown.

I was exploring if a new formula field that is not actually present in the model, might also have 'Available values'.Thanks for your response.

sql

No available values in Report Builder parameter

In a Report builder report I have runtime prompt filter on a field.
But I am not able to see the available values in the dropdown for this field.
My filter is on a "New Field" with the formula :
IF(LENGTH(FieldName)>0,FieldName,("Blank"))

However if my filter is on the FieldName directly and not on the New Field with the above formula I do get the available values.
Is there any way I can get the available values in a dropdown for the New Field?

In Model Designer, add the "New Field" formula as a new attribute to the entity. Then set ValueSelection of the attribute to be dropdown.|||

True any field which is present in the Model can be shown in the dropdown.

I was exploring if a new formula field that is not actually present in the model, might also have 'Available values'.Thanks for your response.

No "From" field on the subscriptions page

We are starting to use data-driven subscriptions for multiple applications
and would like to change the From address for each subscription. There isn't
a "From" field on the "Delivery extension settings for Report Server Email"
page in the Data-driven subscriptions section of Reporting Manager. Let me
know if you've found a solution.
I don't get why the "From" field wasn't included in the interface;
especially, if it is walking across smtp.
--
Any and all contributions are greatly appreciated ...
Regards TJHello,
"Delivery extension settings" does not include "From" settings. This email
address is configured under
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer
You can find search "administrator@." to find the From settings under Report
Server Email settings.
Hope this helps.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
--
| From: "TJ" <nospam@.nowhere.com>
| Subject: No "From" field on the subscriptions page
| Date: Thu, 14 Oct 2004 17:02:09 -0400
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 38.116.25.196
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14|||Thanks and yes I see the From setting, but that doesn't tell me how each
report can have its own From setting. Please advise.
We would like to configure the system to have .
Billing Reports should come from "BillingServer"
HR Reports should come from "HR"
Thanks again
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl...
> Hello,
> "Delivery extension settings" does not include "From" settings. This email
> address is configured under
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer
> You can find search "administrator@." to find the From settings under
Report
> Server Email settings.
> Hope this helps.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> --
> | From: "TJ" <nospam@.nowhere.com>
> | Subject: No "From" field on the subscriptions page
> | Date: Thu, 14 Oct 2004 17:02:09 -0400
> | Lines: 14
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 38.116.25.196
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:32194
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | We are starting to use data-driven subscriptions for multiple
applications
> | and would like to change the From address for each subscription. There
> isn't
> | a "From" field on the "Delivery extension settings for Report Server
> Email"
> | page in the Data-driven subscriptions section of Reporting Manager. Let
me
> | know if you've found a solution.
> |
> | I don't get why the "From" field wasn't included in the interface;
> | especially, if it is walking across smtp.
> |
> | --
> | Any and all contributions are greatly appreciated ...
> | Regards TJ
> |
> |
> |
>|||Hello,
Thank you for your reply. Based on my research, Report server e-mail
delivery extension built in Reporting service does not support this feature
for now. You may need to develop customized e-mail delivery extension. I
have incuded the following articles for your reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_extend_deliver_66xy.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/
rsp_ref_soapapi_service_ak_1xfd.asp
http://msdn.microsoft.com/msdnmag/issues/04/08/SQLServerReportingServices/de
fault.aspx
Also, In addition, you are welcome to send suggestions for product
enhancements that you would like to see in
future versions of Microsoft products to us by submitting MS Wish at
MSWISH@.microsoft.com
Best Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online 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.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "TJ" <nospam@.nowhere.com>
| References: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
<xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl>
| Subject: Re: No "From" field on the subscriptions page
| Date: Fri, 15 Oct 2004 10:54:31 -0400
| Lines: 75
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <uz7jebssEHA.2316@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 38.116.25.196
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:32262
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Thanks and yes I see the From setting, but that doesn't tell me how each
| report can have its own From setting. Please advise.
|
| We would like to configure the system to have .
| Billing Reports should come from "BillingServer"
| HR Reports should come from "HR"
|
| Thanks again
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl...
| > Hello,
| >
| > "Delivery extension settings" does not include "From" settings. This
email
| > address is configured under
| >
| > C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
| > Services\ReportServer\RSReportServer
| >
| > You can find search "administrator@." to find the From settings under
| Report
| > Server Email settings.
| >
| > Hope this helps.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > --
| > | From: "TJ" <nospam@.nowhere.com>
| > | Subject: No "From" field on the subscriptions page
| > | Date: Thu, 14 Oct 2004 17:02:09 -0400
| > | Lines: 14
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| > | Message-ID: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: 38.116.25.196
| > | Path:
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14
| > phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl
| microsoft.public.sqlserver.reportingsvcs:32194
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | We are starting to use data-driven subscriptions for multiple
| applications
| > | and would like to change the From address for each subscription. There
| > isn't
| > | a "From" field on the "Delivery extension settings for Report Server
| > Email"
| > | page in the Data-driven subscriptions section of Reporting Manager.
Let
| me
| > | know if you've found a solution.
| > |
| > | I don't get why the "From" field wasn't included in the interface;
| > | especially, if it is walking across smtp.
| > |
| > | --
| > | Any and all contributions are greatly appreciated ...
| > | Regards TJ
| > |
| > |
| > |
| >
|
|
||||Thanks for your help.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:G7IjqsOtEHA.836@.cpmsftngxa06.phx.gbl...
> Hello,
> Thank you for your reply. Based on my research, Report server e-mail
> delivery extension built in Reporting service does not support this
feature
> for now. You may need to develop customized e-mail delivery extension. I
> have incuded the following articles for your reference:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
> rsp_prog_extend_deliver_66xy.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/
> rsp_ref_soapapi_service_ak_1xfd.asp
>
http://msdn.microsoft.com/msdnmag/issues/04/08/SQLServerReportingServices/de
> fault.aspx
> Also, In addition, you are welcome to send suggestions for product
> enhancements that you would like to see in
> future versions of Microsoft products to us by submitting MS Wish at
> MSWISH@.microsoft.com
> Best Regards,
> Peter Yang
> MCSE2000, MCSA, MCDBA
> Microsoft Partner Online 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.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> --
> | From: "TJ" <nospam@.nowhere.com>
> | References: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
> <xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl>
> | Subject: Re: No "From" field on the subscriptions page
> | Date: Fri, 15 Oct 2004 10:54:31 -0400
> | Lines: 75
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <uz7jebssEHA.2316@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 38.116.25.196
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:32262
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Thanks and yes I see the From setting, but that doesn't tell me how each
> | report can have its own From setting. Please advise.
> |
> | We would like to configure the system to have .
> | Billing Reports should come from "BillingServer"
> | HR Reports should come from "HR"
> |
> | Thanks again
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl...
> | > Hello,
> | >
> | > "Delivery extension settings" does not include "From" settings. This
> email
> | > address is configured under
> | >
> | > C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> | > Services\ReportServer\RSReportServer
> | >
> | > You can find search "administrator@." to find the From settings under
> | Report
> | > Server Email settings.
> | >
> | > Hope this helps.
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | >
> | > --
> | > | From: "TJ" <nospam@.nowhere.com>
> | > | Subject: No "From" field on the subscriptions page
> | > | Date: Thu, 14 Oct 2004 17:02:09 -0400
> | > | Lines: 14
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | > | Message-ID: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: 38.116.25.196
> | > | Path:
> | >
> |
>
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14
> | > phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl
> | microsoft.public.sqlserver.reportingsvcs:32194
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | We are starting to use data-driven subscriptions for multiple
> | applications
> | > | and would like to change the From address for each subscription.
There
> | > isn't
> | > | a "From" field on the "Delivery extension settings for Report Server
> | > Email"
> | > | page in the Data-driven subscriptions section of Reporting Manager.
> Let
> | me
> | > | know if you've found a solution.
> | > |
> | > | I don't get why the "From" field wasn't included in the interface;
> | > | especially, if it is walking across smtp.
> | > |
> | > | --
> | > | Any and all contributions are greatly appreciated ...
> | > | Regards TJ
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Welcome!
Best Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online 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.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "TJ" <nospam@.nowhere.com>
| References: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
<xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl>
<uz7jebssEHA.2316@.TK2MSFTNGP12.phx.gbl>
<G7IjqsOtEHA.836@.cpmsftngxa06.phx.gbl>
| Subject: Re: No "From" field on the subscriptions page
| Date: Tue, 19 Oct 2004 17:44:41 -0400
| Lines: 153
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OtQMYTitEHA.3604@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 38.116.25.196
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:32558
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Thanks for your help.
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:G7IjqsOtEHA.836@.cpmsftngxa06.phx.gbl...
| > Hello,
| >
| > Thank you for your reply. Based on my research, Report server e-mail
| > delivery extension built in Reporting service does not support this
| feature
| > for now. You may need to develop customized e-mail delivery extension.
I
| > have incuded the following articles for your reference:
| >
| >
|
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
| > rsp_prog_extend_deliver_66xy.asp
| >
| >
|
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/
| > rsp_ref_soapapi_service_ak_1xfd.asp
| >
| >
|
http://msdn.microsoft.com/msdnmag/issues/04/08/SQLServerReportingServices/de
| > fault.aspx
| >
| > Also, In addition, you are welcome to send suggestions for product
| > enhancements that you would like to see in
| >
| > future versions of Microsoft products to us by submitting MS Wish at
| > MSWISH@.microsoft.com
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000, MCSA, MCDBA
| > Microsoft Partner Online 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.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| >
| > --
| > | From: "TJ" <nospam@.nowhere.com>
| > | References: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
| > <xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl>
| > | Subject: Re: No "From" field on the subscriptions page
| > | Date: Fri, 15 Oct 2004 10:54:31 -0400
| > | Lines: 75
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| > | Message-ID: <uz7jebssEHA.2316@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: 38.116.25.196
| > | Path:
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12
| > phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl
| microsoft.public.sqlserver.reportingsvcs:32262
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Thanks and yes I see the From setting, but that doesn't tell me how
each
| > | report can have its own From setting. Please advise.
| > |
| > | We would like to configure the system to have .
| > | Billing Reports should come from "BillingServer"
| > | HR Reports should come from "HR"
| > |
| > | Thanks again
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:xTtTmsosEHA.3644@.cpmsftngxa10.phx.gbl...
| > | > Hello,
| > | >
| > | > "Delivery extension settings" does not include "From" settings. This
| > email
| > | > address is configured under
| > | >
| > | > C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
| > | > Services\ReportServer\RSReportServer
| > | >
| > | > You can find search "administrator@." to find the From settings under
| > | Report
| > | > Server Email settings.
| > | >
| > | > Hope this helps.
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > Get Secure! - www.microsoft.com/security
| > | >
| > | > --
| > | > | From: "TJ" <nospam@.nowhere.com>
| > | > | Subject: No "From" field on the subscriptions page
| > | > | Date: Thu, 14 Oct 2004 17:02:09 -0400
| > | > | Lines: 14
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| > | > | Message-ID: <eZCBTEjsEHA.3412@.TK2MSFTNGP14.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: 38.116.25.196
| > | > | Path:
| > | >
| > |
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14
| > | > phx.gbl
| > | > | Xref: cpmsftngxa10.phx.gbl
| > | microsoft.public.sqlserver.reportingsvcs:32194
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | We are starting to use data-driven subscriptions for multiple
| > | applications
| > | > | and would like to change the From address for each subscription.
| There
| > | > isn't
| > | > | a "From" field on the "Delivery extension settings for Report
Server
| > | > Email"
| > | > | page in the Data-driven subscriptions section of Reporting
Manager.
| > Let
| > | me
| > | > | know if you've found a solution.
| > | > |
| > | > | I don't get why the "From" field wasn't included in the interface;
| > | > | especially, if it is walking across smtp.
| > | > |
| > | > | --
| > | > | Any and all contributions are greatly appreciated ...
| > | > | Regards TJ
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

Next Item in String

I'm creating a report and wish to use field {Session_Starts.Special_Date}. It is a string field and I want to create a formula where this field automatically draws in the next string item.
Example if the special date is March 26, 2007, I want it to use the next string item, which is April 9, 2007. I can't use +14, because the next string item isn't always +14 days.
Can anyone help me with the formula to use?
Thanks so much in advance!So you want the {Session_Starts.Special_Date} value of the next record? Use the Next function! Look in the help.

Next Friday

I have a table called Order, within which there is a field called orderdate,
I can I program so that I can always retrieve order with an order date as
next Friday when I am running this script anytime this w.
Thanks,
QjleeHi
You can work this out using a calendar table see
http://www.aspfaq.com/show.asp?id=2519 or use the dataadd and datepart
functions (see books online).
John
"qjlee" wrote:

> I have a table called Order, within which there is a field called orderdat
e,
> I can I program so that I can always retrieve order with an order date as
> next Friday when I am running this script anytime this w.
> Thanks,
> Qjlee|||You can solve it by using the DATEPART() function like this:
declare @.today smalldatetime
set @.today = '20051102'
create table #tmp
(
ID int identity(1,1) primary key clustered,
OrderDate smalldatetime null
)
set nocount on
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051102')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051107')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051109')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
set nocount off
select * from #tmp
where OrderDate =
(
select min(OrderDate) from #tmp -- Earliest...
where datepart(dw,OrderDate) = 6 -- Friday...
and OrderDate >= @.today -- on or after "today"
)
drop table #tmp
The DATEPART() function, when used with the 'dw' parameter, returns the
day of the w and is dependant on how you have the SET DATEFIRST
setting configured. For me, Friday = 6. Also, if your OrderDate column
contains time info as well then you'll need to do a little range
checking to make sure the OrderDate is somewhere on that day (and not
just at midnight) - there are heaps of references on how to do that (I'm
sure I've seen an example on http://aspfaq.com).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||Qjlee,
This should always give you the Friday in the w following
the current one, assuming your w begins on Monday.
dateadd(d,datediff(d,'19000101',getdate(
))/7*7+11,'19000101')
Steve Kass
Drew University
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this instead:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,CURRENT_TIMESTAMP))
Thanks,
M. E. Houston
"SQLChallenge" <sqlchallenge@.saikoconsulting.com> wrote in message
news:1131062936.928610.144390@.g47g2000cwa.googlegroups.com...
> Try this:
> SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
> --
> Mark Graveline
> Take The Challenge
> http://www.sqlchallenge.com
> --
>

Newbie: Updating record with User information

Hello,

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd like to
also update this value when the record is changed. If I understand things
correctly, I'll need a trigger to do this, but I've never created a trigger
before. Is this an easy thing to accomplish?

Thanks!

RickRico (you@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol

SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)

The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.

A trigger fires once per statement, so there can be many rows in these
tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That's excellent! Thank you very much for your help Erland, that was
exactly what I needed! (that will make coding in VB much easier. ;)

Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98ADF09B38EBDYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (you@.me.com) writes:

Quote:

Originally Posted by

>I have a table that has a UserUpdated field which has a default value of
>SUSER_SNAME(). This works great when the record is created, but I'd
>like to also update this value when the record is changed. If I
>understand things correctly, I'll need a trigger to do this, but I've
>never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jan 3, 4:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Rico (y...@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdatedfieldwhich has adefaultvalueof
SUSER_SNAME(). This works great when the record is created, but I'd
like to alsoupdatethisvaluewhen the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATEASUPDATEtbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

Thanks.
Rayne|||Rayne (wifetalks@.gmail.com) writes:

Quote:

Originally Posted by

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

No, but you could write a program to generate them.

You could also consider third-party tools ApexSql (www.apexsql.com)
has SQL Audit, for instance. I have not tried it myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx