change font color in subtotal

My matrix looks like this:
row1
row2
subtotal
The subtotal background color is different than the regular row and this
part is working, I also want to change the font color of the subtotal based
on the subtotal value.
=IIF(
Parameters!RP_PERCENNUM.Value=1,(IIF(SUM(Fields!Percentage.Value)>100,"Red","Transparent")),(IIF(SUM( Fields!Hours.Value)>40,"Red","Transparent")))
The above statement won't work and it changes everything to red. I have
tried to use InScope and it did the same thing.
=IIF(
Parameters!RP_PERCENNUM.Value=1,(IIF(InScope(SUM(Fields!Percentage.Value))>"100","Red","Transparent")),(IIF(InScope(SUM(
Fields!Hours.Value))>"40","Red","Transparent")))
Anyone has any idea how to change the color? Thanks.You would need to nest your IIF's inside of another IIF
or
write code in the Report Code field, with input parameters, for Percentage
and Hours, and a return parameter of your font color. Then call this code
from the font color property as an expression
"Helen" wrote:
> My matrix looks like this:
> row1
> row2
> subtotal
> The subtotal background color is different than the regular row and this
> part is working, I also want to change the font color of the subtotal based
> on the subtotal value.
> =IIF(
> Parameters!RP_PERCENNUM.Value=1,(IIF(SUM(Fields!Percentage.Value)>100,"Red","Transparent")),(IIF(SUM( Fields!Hours.Value)>40,"Red","Transparent")))
> The above statement won't work and it changes everything to red. I have
> tried to use InScope and it did the same thing.
> =IIF(
> Parameters!RP_PERCENNUM.Value=1,(IIF(InScope(SUM(Fields!Percentage.Value))>"100","Red","Transparent")),(IIF(InScope(SUM(
> Fields!Hours.Value))>"40","Red","Transparent")))
> Anyone has any idea how to change the color? Thanks.
>

Change default input parameter drop down list size

I am working on a report which uses a multi value input parameter. The value
set is depend on the other input parameter, so the size and value are
different when the value of the other parameter has changed. The problem is
sometime it works perfectly, but the other time when there are only two
value, the drop down list will show "Select All" and first item and gives me
a scroll bar. This is very annoying. Anyone know how to make the drop down
list to disply more desirous number of items. In addition, is there a way to
control the parameter text box length?I have the same problem but even a worse scenario. My list is filtered by
another and can end up with only one entry. While two entries at least lets
you scroll through the list ('cause the scroll buttons are shown), a list of
only one value doesn't even show the scroll buttons.
I also need a way to allow/force a list this short to be at least long
enough to show the scrollbars.
Jon

Change datatype from varchar to bigint not working

Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi

This should help you:
http://www.sqlteam.com/article/default-constraint-names

Change Dataset font size

Is there there a way to change font size of the text of the data set while working in the Report Designer?

In the menu: Go to "Tools" --> "Options"; then under "Environment" --> "Fonts and Colors"

You can edit alot of elements within the dev. studio. (I just didn't find the way to change the font size of the generic query builder (maybe that only happens after restart which I didn't do))

Change config file

Do I have to reboot after changing RSReportServer.config?
I am working with the alias and host names. I have the alias set to
true, but it only puts in john_g.
I need it to add '@.domain.com' Can that be done in the config using the
DefaultHostName? If so, just put domain.com in?
Thanks.You don't have to reboot, but you should restart the ReportServer service
(in Control Panel) after changing config files.
You might also want to run iisreset.exe, depending on the changes you are
making -- but that's more for when you have GUI code changes or are tweaking
custom extensions.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>|||Yes, you want to use the defaultHostName element. You do not need to reboot
after changing the config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

Certification: Looking for advise

Hi All

I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.

My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.

Have found this two books in Amazon:

1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition

2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition

However their rating didn't encourage me to place directly my orders.

Does someone have any experience with them ?

Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?

Do you know some others books you woul rather encourage me to buy ?

Any advice in this regards really appreciated.Transcender

www.transcender.com

Regards,

hmscott|||The MS Press books are not the best for learning. Try the Que or Sybex books for learning the material while preparing for certification. Make sure you get hands-on experience with all the examples and labs as you read the books. Use Transcender after reading the books to assess your readiness to take the test. Good luck!

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

CE DB requires SQL Express install on client machines?

I'm working on a C# 2008 project, when i add a CE Database to it, then publish the app, the installer wants to download and install the entire SQL Server Express product on the client machines. My understanding is that I should be able to embed this database right into the app, but it defeats the purpose to have the installer download and install the entire express product (the actual project is 1 meg).

Is this expected behavior? or am I doing something wrong?

SQL Express is NOT required for SQL Compact projects. You must have a reference to a .mdf file or similar in your project that triggers this requirement.|||

I checked the prerequisite tab and sql express isnt even checked, i'll investigate further

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stu
Stu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Conde?a
"Stu" wrote:

> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail used
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>
|||Paulo,
thank you for that, that has worked.
stu
"Paulo Conde?a" wrote:
[vbcol=seagreen]
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Conde?a
> "Stu" wrote:
|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Conde?a
"Stu" wrote:
[vbcol=seagreen]
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Conde?a" wrote:

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stuStu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Condeça
"Stu" wrote:
> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail used
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>|||Paulo,
thank you for that, that has worked.
stu
"Paulo Condeça" wrote:
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Condeça
> "Stu" wrote:
> > Hi there,
> >
> > I have CDOSYS email working and correclty firing off emails for configured
> > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > maintenance plan job successfully backups all user databases (we have
> > aproximately 16). what i'm after is the same information that sql mail used
> > to send:
> >
> > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > run on 19/11/2006 at 19:00:01
> > DURATION: 0 hours, 15 minutes, 1 seconds
> > STATUS: Succeeded
> > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > (Schedule 1). The last step to run was step 1 (Step 1).
> >
> >
> > the closest i can get to this is to use the following script as the next
> > step after the backups have completed:
> >
> > DECLARE @.msg nvarchar(4000)
> > Set @.msg = REPLACE("Error: [A-ERR]
> > Severity: [A-SEV]
> > Date: [STRTDT]
> > Time: [STRTTM]
> > Database: [A-DBN]
> > Message: [A-MSG] ", "'", "") --'
> >
> > EXEC sp_send_cdosysmail 'fromaddress',
> > 'toaddress',
> > 'subject line',
> > @.msg
> >
> > but all this produces is the following:
> >
> > Error:
> > Severity:
> > Date: 20061120
> > Time: 111358
> > Database:
> > Message:
> >
> > i dont really want to create separate jobs and alerts for each individual
> > database so i was wondering if anyone had any ideas at all?!
> >
> > thanks in advance
> >
> > stu
> >|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Condeça
"Stu" wrote:
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Condeça" wrote:
> > Stu,
> >
> > What you can do is the following :
> > In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> > and associate the failure or the sucess of the maitenance plan step to the
> > respective step ( sucess or failure ).
> >
> > Best Regards,
> >
> > Paulo Condeça
> >
> > "Stu" wrote:
> >
> > > Hi there,
> > >
> > > I have CDOSYS email working and correclty firing off emails for configured
> > > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > > maintenance plan job successfully backups all user databases (we have
> > > aproximately 16). what i'm after is the same information that sql mail used
> > > to send:
> > >
> > > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > > run on 19/11/2006 at 19:00:01
> > > DURATION: 0 hours, 15 minutes, 1 seconds
> > > STATUS: Succeeded
> > > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > > (Schedule 1). The last step to run was step 1 (Step 1).
> > >
> > >
> > > the closest i can get to this is to use the following script as the next
> > > step after the backups have completed:
> > >
> > > DECLARE @.msg nvarchar(4000)
> > > Set @.msg = REPLACE("Error: [A-ERR]
> > > Severity: [A-SEV]
> > > Date: [STRTDT]
> > > Time: [STRTTM]
> > > Database: [A-DBN]
> > > Message: [A-MSG] ", "'", "") --'
> > >
> > > EXEC sp_send_cdosysmail 'fromaddress',
> > > 'toaddress',
> > > 'subject line',
> > > @.msg
> > >
> > > but all this produces is the following:
> > >
> > > Error:
> > > Severity:
> > > Date: 20061120
> > > Time: 111358
> > > Database:
> > > Message:
> > >
> > > i dont really want to create separate jobs and alerts for each individual
> > > database so i was wondering if anyone had any ideas at all?!
> > >
> > > thanks in advance
> > >
> > > stu
> > >

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stuStu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Conde?a
"Stu" wrote:

> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail us
ed
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>|||Paulo,
thank you for that, that has worked.
stu
"Paulo Conde?a" wrote:
[vbcol=seagreen]
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure )
,
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Conde?a
> "Stu" wrote:
>|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Conde?a
"Stu" wrote:
[vbcol=seagreen]
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Conde?a" wrote:
>

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.From varchar(100) , @.To varchar(100) , @.Subject varchar(100), @.Body
varchar(4000)
/ ****************************************
**
This stored procedure takes the parameters and sends an e-mail. All the mail
configurations are hard-coded in the stored procedure. Comments are added to
the stored procedure where necessary. References to the CDOSYS objects are
at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

CDONS.mail not working

I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>

CDO mail attachment is not working

Hi
I have some SQL code below which I'm running on a server DEV_TESTSTAGE2 with
Windows 2000 and SQL Server 7. I'm trying to send an attachment which works
fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
somewhere else on the network it send the email without the attachment and I
get the error message below. I've had a look on the internet but most of the
web peges refer to permissions problems in ASP. In my case I'm using SQL
Server objects and I'm running the query with Windows authentication and my
NT logon id
has access to the file attachement . I've tried running the query using SQL
authentication but I get the same error.
Any help would be gratefully appreciated
-2147024891
Source: CDO.Message.1
Description: Access is denied
declare @.HResult int
declare @.HR int
declare @.iMsg int
declare @.Text varchar(8000)
Declare @.source varchar(255)
Declare @.output varchar(1000)
Declare @.description varchar(500)
declare @.attachfile varchar(1000)
--************* Create the CDO.Message Object ************************
exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
--error handling for failure to create object......
select @.HResult, @.iMsg
--***************Configuring the Message Object ******************
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value','CHOPGBBES001.gb.cannonsgroup.net'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rverport").Value','25'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpus
essl").Value','False'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpco
nnectiontimeout").Value','60'
-- Save the configurations to the message object.
EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
set @.attachfile = '\\flavius\IT\pstat.txt'
--set @.attachfile = 'c:\boot.ini'
EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
RonanRonan
Do you have any permissions issue to the folder/file?
in addition ,take a look at
http://www.sqldev.net/xp/xpsmtp.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
> Hi
> I have some SQL code below which I'm running on a server DEV_TESTSTAGE2
> with
> Windows 2000 and SQL Server 7. I'm trying to send an attachment which
> works
> fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
> somewhere else on the network it send the email without the attachment and
> I
> get the error message below. I've had a look on the internet but most of
> the
> web peges refer to permissions problems in ASP. In my case I'm using SQL
> Server objects and I'm running the query with Windows authentication and
> my
> NT logon id
> has access to the file attachement . I've tried running the query using
> SQL
> authentication but I get the same error.
> Any help would be gratefully appreciated
> -2147024891
> Source: CDO.Message.1
> Description: Access is denied
>
> declare @.HResult int
> declare @.HR int
> declare @.iMsg int
> declare @.Text varchar(8000)
> Declare @.source varchar(255)
> Declare @.output varchar(1000)
> Declare @.description varchar(500)
> declare @.attachfile varchar(1000)
>
> --************* Create the CDO.Message Object ************************
> exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
>
> --error handling for failure to create object......
> select @.HResult, @.iMsg
>
> --***************Configuring the Message Object ******************
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/send
using").Value','2'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
server").Value','CHOPGBBES001.gb.cannonsgroup.net'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
serverport").Value','25'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
usessl").Value','False'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
connectiontimeout").Value','60'
>
> -- Save the configurations to the message object.
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
>
> -- Set the e-mail parameters.
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
> set @.attachfile = '\\flavius\IT\pstat.txt'
> --set @.attachfile = 'c:\boot.ini'
> EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
>
> --
> Ronan|||Hi Uri
Yes I should have mentioned I do have access to the file...it has 'Everyone'
permission
I had a look at the article you sent me...it refers to xp_smtp_sendmail...I
can't find that extended stored procedure in the master database of SQL
Server 2000...I have xp_sendmail but that requires you set up
a MAPI client which I don't want to do I want to send it via SMTP
Ronan
"Uri Dimant" wrote:

> Ronan
> Do you have any permissions issue to the folder/file?
> in addition ,take a look at
> http://www.sqldev.net/xp/xpsmtp.htm
>
>
> "Ronan" <Ronan@.discussions.microsoft.com> wrote in message
> news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
>
>|||I have used the xp_smtpsendmail extended stored procedure. It works well,
but you will have to download the .ddl ..google it, and you'll find it. I've
used it to send reports generated as text files to business managers in the
past with great success...you will have to copy it to your sql\bin folder,
and registewr it inside sql, to get it to work...easy to do.
"Ronan" wrote:
> Hi Uri
> Yes I should have mentioned I do have access to the file...it has 'Everyon
e'
> permission
> I had a look at the article you sent me...it refers to xp_smtp_sendmail...
I
> can't find that extended stored procedure in the master database of SQL
> Server 2000...I have xp_sendmail but that requires you set up
> a MAPI client which I don't want to do I want to send it via SMTP
> --
> Ronan
>
> "Uri Dimant" wrote:
>|||Hi Tom
I downloaded and installed xp_smtp_sendmail and again it works fine for
attachements
which are on the machine where xp_smtp_sendmail is registered but it
still doesn't seem
to work when the attachment is on a different machine. I think I'll just
have to put my attachments
on the machine where xp_smtp_sendmail is registered as a work around.
Thanks for your help.
Regards
Ronan
Ronan
"Tom Mongold" wrote:
> I have used the xp_smtpsendmail extended stored procedure. It works well,
> but you will have to download the .ddl ..google it, and you'll find it. I'
ve
> used it to send reports generated as text files to business managers in th
e
> past with great success...you will have to copy it to your sql\bin folder
,
> and registewr it inside sql, to get it to work...easy to do.
> "Ronan" wrote:
>

Categories and Sub Categories

Hello,
I am working on a web site that makes documents available to students.
I am using tags to classify each document content.
So I have the following tables:
Documents, Tags, DocumentsTags
I also need to categorize the documents:
Subject
|-- Level
|-- Year
For example:
Document_1 - Math > University Level > Second Year
How can I create a table to create such classification?
Thank You,
MiguelHere is one way.
Have a table called Subjects, and add SubjectID to the Documents table with
a foreign key to Subjects (so that you only store "Math" once and it is easy
to update).
Add a column called Level to the Documents table.
Add a column called Year to the Documents table.
"shapper" <mdmoura@.gmail.com> wrote in message
news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> Hello,
> I am working on a web site that makes documents available to students.
> I am using tags to classify each document content.
> So I have the following tables:
> Documents, Tags, DocumentsTags
> I also need to categorize the documents:
> Subject
> |-- Level
> |-- Year
> For example:
> Document_1 - Math > University Level > Second Year
> How can I create a table to create such classification?
> Thank You,
> Miguel|||On Apr 24, 1:31 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Here is one way.
> Have a table called Subjects, and add SubjectID to the Documents table with
> a foreign key to Subjects (so that you only store "Math" once and it is easy
> to update).
> Add a column called Level to the Documents table.
> Add a column called Year to the Documents table.
> "shapper" <mdmo...@.gmail.com> wrote in message
> news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> > Hello,
> > I am working on a web site that makes documents available to students.
> > I am using tags to classify each document content.
> > So I have the following tables:
> > Documents, Tags, DocumentsTags
> > I also need to categorize the documents:
> > Subject
> > |-- Level
> > |-- Year
> > For example:
> > Document_1 - Math > University Level > Second Year
> > How can I create a table to create such classification?
> > Thank You,
> > Miguel
In the future I might need other categories. I was thinking in
something more flexible then that. Something like:
http://www.sqllessons.com/categories.html
I have two other tables:
Documents > DocumentID, Title, Description
DocumentsCategories > DocumentID, CategoryID
Then I was using Adjacency Model to create the table Categories.
This way I could have a Document associated to one or more categories
and at the same time I could have various levels of categories.
I am using LINQ to SQL so I would like to find a way to make this work
also with LINQ.
I am not sure if this is the best way but after Goggling this is the
closer I could find to solve my problem.
Thanks,
Miguel

casting not working?

Can anyone see what's wrong with this transformation?

((DT_STR)([IN-DLN]))

I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...

Thanks!

Jim Work
What *is* the error exactly?

You might want to just try:
(DT_STR)[IN-DLN]|||"Attempt to parse the expression "(DT_STR)[IN-DLN]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or it might be missing part of a required element such as a parenthesis."

Any ideas? Your suggested syntax didn't work, either.
|||Silly me...

(DT_STR,length in bytes,1252)[IN-DLN]|||When looking at the list of available Type Casts, you can see that the DT_STR type cast requires two arguments.|||Thanks as ever, Phil!

Jim Work

Cast and Convert

I am trying to convert an INT to a Date value with is currently working with
the following query
SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
However, the Date format is YYYY-MM-DD Time
Can I use Convert and Cast in the same statement. I really needs the dates
in DD/MM/YY format
Any help glady appreciated.
ThanksHow does the INT look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>I am trying to convert an INT to a Date value with is currently working wit
h the following query
> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
> However, the Date format is YYYY-MM-DD Time
> Can I use Convert and Cast in the same statement. I really needs the date
s in DD/MM/YY format
> Any help glady appreciated.
> Thanks
>|||19740121
So this should display 21/01/1974
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
> How does the INT look like?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>|||So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to C
HAR(8) and that format
is one of the formats which is language independent regarding datetime conve
rsions from string to
datetime. Try below:
SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
I.e., cast int to string, then string to datetime, then datetime to string (
using a conversion
code).
The question is, of course, why you store dates as int instead of datetime..
. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
> 19740121
> So this should display 21/01/1974
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
>|||Thanks it has worked.. I am sure I tried this :-) Luckily I didn't design
this application. I don't know why they chose INT instead of a datetime
field. :-(
Cheers
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epNDnLqDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to
> CHAR(8) and that format is one of the formats which is language
> independent regarding datetime conversions from string to datetime. Try
> below:
> SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
> I.e., cast int to string, then string to datetime, then datetime to string
> (using a conversion code).
> The question is, of course, why you store dates as int instead of
> datetime... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
>

Cast and Convert

I am trying to convert an INT to a Date value with is currently working with
the following query
SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
However, the Date format is YYYY-MM-DD Time
Can I use Convert and Cast in the same statement. I really needs the dates
in DD/MM/YY format
Any help glady appreciated.
Thanks
How does the INT look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>I am trying to convert an INT to a Date value with is currently working with the following query
> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
> However, the Date format is YYYY-MM-DD Time
> Can I use Convert and Cast in the same statement. I really needs the dates in DD/MM/YY format
> Any help glady appreciated.
> Thanks
>
|||19740121
So this should display 21/01/1974
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
> How does the INT look like?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>
|||So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to CHAR(8) and that format
is one of the formats which is language independent regarding datetime conversions from string to
datetime. Try below:
SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
I.e., cast int to string, then string to datetime, then datetime to string (using a conversion
code).
The question is, of course, why you store dates as int instead of datetime... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
> 19740121
> So this should display 21/01/1974
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
>
|||Thanks it has worked.. I am sure I tried this :-) Luckily I didn't design
this application. I don't know why they chose INT instead of a datetime
field. :-(
Cheers
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epNDnLqDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to
> CHAR(8) and that format is one of the formats which is language
> independent regarding datetime conversions from string to datetime. Try
> below:
> SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
> I.e., cast int to string, then string to datetime, then datetime to string
> (using a conversion code).
> The question is, of course, why you store dates as int instead of
> datetime... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
>