Quickie: SMTP config for attachments

I can configure a working subscription to e-mail through my SMTP server when
sending only the LINK to a report but not if I attach the report to the
e-mail in say excel or xml format.
Any suggestions?
Thanks
TravisSomeone must have an idea... please! :-)
I have tested using a command line SMTP utility and have been able to send
both a plain text message and a plain text message wth and attachment so I
know the Mail server will accept it.
Someone, Anyone?
Thanks
Travis
"REM7600" <rem7600@.hotmail.com> wrote in message
news:eumyP3OdEHA.1692@.tk2msftngp13.phx.gbl...
> I can configure a working subscription to e-mail through my SMTP server
when
> sending only the LINK to a report but not if I attach the report to the
> e-mail in say excel or xml format.
> Any suggestions?
> Thanks
> Travis
>|||Here's the error from the log... I can't make much of it... Other than the
part that says please see the log files...
ReportingServicesService!emailextension!728!07/29/2004-07:32:05:: Error
sending email.
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The Report
Server has encountered a configuration error; more details in the log
files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
ception: The Report Server has encountered a configuration error; more
details in the log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntP
tr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(Strin
g userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(S
tring userName, IntPtr userToken, Byte[] secDesc, ReportOperation
requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionF
romCatalog(CatalogItemContext reportContext, String historyID, Boolean
forRendering, Guid& reportID, Int32& executionOption, String&
savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot&
snapshotData, Guid& linkID, DateTime& historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
Service rs, CatalogItemContext reportContext, ClientRequest session,
JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMes
sageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessag
e(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Noti
fication notification)

Quick and easy question, Sql update method . . .

I am new to Sql, so I think this is a really easy question.

But I am working on a 2005 MSSQL database.

When I add columns to an existing application with data and tables already being used, and the new column will be set to Database Null.

What is an easy way to quickly add the data to all the rows in the table.

For example if I am adding a checkbox, I've been doing it manually, add the column, change all the rows to False, one by one.

And then I can change it to Disallow DBNull.

As I get more and more users this could be a very time consuming process.

So the name of the Table is classifeds_Ads and let's say the column I want to add is Bonus and it needs to be filled with False.

How do I do this?

Thank you in advance

Daniel Meis

You can create a new query and then just run this:

UPDATE classifieds_Ads Set Bonus = 0

Or an easier approach is to use the Column Properties pane to set the initial properties for the column - Allow Nulls: No, Default Value or Binding: 0

|||

Works great, Thank you.

Queue Functionality

I am working on Queue functionality for my application. Queue is nothing but table where same record should not be processed by 2 different people/machines. To simplify

consider table

CREATE TABLE [dbo].[Table_1](

[Col1] [int] NULL,

[Enabled] [bit] NULL

) ON [PRIMARY]

I have procedure that picks up records and stores in table passed as input.

Different apps running on different machines specify their local tables

Create Procedure [dbo].[spTestQueue]

@.Tbl as varchar(100)

AS

Declare @.No varchar(10)

Select Top 1 @.No= Cast(Col1 as varchar(10)) from Table_1(nolock) Where Enabled = 0

Update Table_1 Set Enabled =1 Where Col1 = Cast(@.No as int)

EXEC ('Insert ' + @.Tbl + ' values(' + @.No + ')')

It worked fine during testing but there is nothing to prevent 2 machines to pick up same records.This is highly transactional table

How to efficiently implent locking or transcation to ensure that same record dose not get processed by 2 machines

You need to use transactions.

Code Snippet

BEGIN TRANSACTION

SELECT ... FROM Table_1 WITH (UPDLOCK) WHERE ...

UPDATE ...

COMMIT TRANSACTION

EXEC ...

I'd suggest your queue should be a separate table which should only contain unprocessed items, so deleting a row would remove it from the queue table. This would scale much better.

Code Snippet

BEGIN TRANSACTION

SELECT ... FROM Table_1 WITH (UPDLOCK) WHERE ...

DELETE FROM Table_1 WHERE ...

COMMIT TRANSACTION

INSERT INTO Store VALUES (...)

EXEC ...

You'll have to be very careful if you encounter any errors, you cannot roll the transaction back.

Hope that helps.

Jamie

|||

To ensure that when the first machine runs the procedure the second one can't read the row that is being updated by the first one, add 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' at the beginning of the stored procedure. Also, add BEGIN TRAN and COMMIT TRAN to the beginning and end of the procedure. Here is the updates:

Code Snippet

Create Procedure [dbo].[spTestQueue]

@.Tbl as varchar(100)

AS

Begin

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

Declare @.No varchar(10)

Select Top 1 @.No= Cast(Col1 as varchar(10)) from Table_1(nolock) Where Enabled = 0

Update Table_1 Set Enabled =1 Where Col1 = Cast(@.No as int)

EXEC ('Insert ' + @.Tbl + ' values(' + @.No + ')')

COMMIT TRANSACTION

End

I hope this answers your question.

Best regards,

Sami Samir

|||

I can think of a couple of solutions depending upon just how active this table is.

If you can afford to serialise the access to this table for this routine then you could use an application lock. This allows you to place a lock (like a critical section) over the pair of operation SELECT and UPDATE. This will prevent 1 app running the SELECT before another has run the update. As long as these run quickly then you will not get excessive contention.

You use the sp_getapplock and sp_releaseapplock procedures.

If you set a reasonable timeout on the the sp_getapplock call then this will cleanly serialise the operations.

If you cannot afford to serialise then I would suggest using a GUID to mark your record and retrieve it. You add a column of type uniqueidentifier to your table which starts off as null. And then you select it in this way. There is a sample of doing that below.

Code Snippet

DECLARE @.Tag_ID as uniqueidentifier

SET @.Tag_ID = NEWID()

UPDATE TOP (1) Table_1

SET Select_Key = @.Tag_ID

WHERE (Select_Key IS NULL)

SELECT @.No = CAST(Col1 as varchar(10))

FROM Table_1 (nolock)

WHERE (Select_Key = @.Tag_ID)

UPDATE Table_1 SET Enabled=1

WHERE (Col1 = CAST(@.No as int))

As the guid will be unique you will always get the record and noone else will get it. I have left it using enabled to mark when a record has been taken up.

|||

Went off and found my old applock code. This is a sample of how to use applocks to serialise the multiple runs. It will wait 5 seconds before failing. Any application lock (with owner of transaction) is automatically released if the transaction is committed or rolled back.

Code Snippet

-- Start a transaction and lock the App object
BEGIN TRANSACTION
-- Attempt to acquire the Table_1 queue processing Application Lock
EXEC @.lResVal = sp_getapplock
@.Resource = 'Table_1-QueueProcess',
@.LockMode = 'Exclusive',
@.LockOwner = 'Transaction',
@.LockTimeout = 5000

-- Check for failure
IF (@.lResVal >= 0)
BEGIN
-- The lock was acquired
-- Perform the acquisition of a record
SELECT TOP (1) ....
.
.
UPDATE Table_1 ....

-- Commit the transaction (will release lock) as record has been acquired
COMMIT TRANSACTION

-- Run the rest of the code
EXEC (' ....

END

ELSE
BEGIN
-- Failure to lock the sequence resource - so no action
ROLLBACK TRANSACTION
END

|||I thought about using Isolation level SERIALIZABL. But I don't think that will scale since this is highly transactional table|||

First, if you are using SQL Server 2005 then please don't spend time reinventing the wheel but instead use the Service Broker functionality that is built into the database engine. This gives a scalable queueing infrastructure among other things. See Books Online for more details.

If you are on older version of SQL Server then you can do below instead without need for doing the SELECT:

Code Snippet

DECLARE @.Col1 int;

SET ROWCOUNT 1;

UPDATE Table_1

SET @.Col1 = Col1

, Enabled = 1

WHERE Enabled = 0;

SET ROWCOUNT 0;

The UPDATE statement takes exclusive lock on the row so there will be no conflict. You can simplify it in SQL Server 2005 using the TOP clause like:

Code Snippet

UPDATE TOP(1) Table_1

SET @.Col1 = Col1

, Enabled = 1

WHERE Enabled = 0;