Help!!!!--Problems about the multi column report!

I have a 2 coulmns report ,one group ,when I print the report,it does not paging correctly.For example,suppose every column can display 20 rows, if every group has 20-40 rows ,the print is ok,it can paging correctly,but if one group has 10 rows or more than 40 and less than 60,the two groups will print on one paper,how can I print the every group on different paper?

Any advice would be much appreciated.

Thanks.

No one can answer me?|||

Garlic,

You can edit your group properties, On your group, right click, select edit group, on General tab, select "Page break at start" this should give you a new page for each group change.

HELP! CDONTS Attachment in SQL server

Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

I have been trying several methods and i'm stuck

Right now, I'm using the next SP:

DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)

DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)

EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT

IF @.Hresult = 0 begin
--SET SOME PROPERTIES

SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY

EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0

--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL

--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end

Thanks in advance for your helpHave you tried the following code for sending the attachment mate?

-- Check for multiple attachments separated by a semi-colon ';'.
If @.vcAttachments is not null
Begin
If right(@.vcAttachments,1) <> ';'
Select @.vcAttachments = @.vcAttachments + '; '
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
While @.iPos > 0
Begin
Select @.vcAttachment = ltrim(rtrim(substring(@.vcAttachments, 1, @.iPos -1)))
Select @.vcAttachments = substring(@.vcAttachments, @.iPos + 1, Len(@.vcAttachments)-@.iPos)
EXEC @.iHr = sp_OAMethod @.iMessageObjId, 'AddAttachment', @.iRtn Out, @.vcAttachment
IF @.iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @.iMessageObjId, @.vcErrSource Out, @.vcErrDescription Out
Select @.vcBody = @.vcBody + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @.vcErrSource + char(13) + char(10) +
@.vcAttachment
End
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
End
End

----------

you can find more info about this at www.sqlservercentral.com

Hope this helps...

have a good one|||Originally posted by saulo70
Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

I have been trying several methods and i'm stuck

Right now, I'm using the next SP:

DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)

DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)

EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT

IF @.Hresult = 0 begin
--SET SOME PROPERTIES

SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY

EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0

--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL

--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end

Thanks in advance for your help

Here;s a sp which will make your life easier ...

CREATE PROCEDURE [dbo].[sp_CDOMail]
@.To varchar(1000),
@.From varchar(100),
@.Subject varchar(100),
@.Body varchar(4000),
@.CC varchar(100) = null,
@.BCC varchar(100) = null,
@.FilePath varchar(100) = Null, --inlcude last slash
@.FileName varchar(100) = Null

/************************************************** *******************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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

DATE By Descript
------ ------ ----------------
11/01/03 SP --Modified to Send Attachments from the original query designed by Microsoft.

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

Declare @.attachment varchar(200)

set @.attachment = @.FilePath + @.FileName

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'Mozart'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
EXEC @.hr = sp_OASetProperty @.iMsg, 'CC', @.CC
EXEC @.hr = sp_OASetProperty @.iMsg, 'BCC', @.BCC

--Add Attachment if exists.
if @.Attachment <> ''
EXEC @.hr = sp_OAMethod @.iMsg, 'AddAttachment',null, @.Attachment , @.FileName

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- 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
SELECT @.output = @.output + '
Description: ' + @.description
Select @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg

GO

IF you still have problems email me @. shaileshpatangay@.hotmail.com

--shailesh|||Thanks a lot for your help

It's working now

Help With Where Clause

I am creating a food database application where I allow the user to search
for food names. For this example, lets supposed that my database has the
following food names on the table.
Salt with water
Chips and salsa
Fried Chicken
Guacamole
If the user types 'sa' on the search textbox I would like to retrieve the
following records:
Salt with water
Chips and salsa
This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
the user typed 'sa ch' (this is the string 'sa' then a space and then the
string 'ch'). I would like to retrieve the records
Salt with water
Chips and salsa
Fried Chicken
I get the same records as before because I included the string 'sa' and I
get the record 'Fried Chicken' because Chicken starts with 'ch'. The problem
is that the user can have as many words as he or she pleases. It even gets
more complicated because I would like to use a store procedure on Microsoft
SQL server.
Could someone help me creating this store procedure, PLEASE! Thank you.Rene
See if this helps
CREATE TABLE #Test
(
col VARCHAR(50)
)
INSERT INTO #Test VALUES ('Salt with water')
INSERT INTO #Test VALUES ('Chips and salsa')
INSERT INTO #Test VALUES ('Fried Chicken')
INSERT INTO #Test VALUES ('Guacamole')
SELECT * FROM #Test WHERE col LIKE '%sa%'
"Rene" <nospam@.nospam.com> wrote in message
news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am creating a food database application where I allow the user to search
> for food names. For this example, lets supposed that my database has the
> following food names on the table.
> Salt with water
> Chips and salsa
> Fried Chicken
> Guacamole
> If the user types 'sa' on the search textbox I would like to retrieve the
> following records:
> Salt with water
> Chips and salsa
> This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
> the user typed 'sa ch' (this is the string 'sa' then a space and then the
> string 'ch'). I would like to retrieve the records
> Salt with water
> Chips and salsa
> Fried Chicken
> I get the same records as before because I included the string 'sa' and I
> get the record 'Fried Chicken' because Chicken starts with 'ch'. The
problem
> is that the user can have as many words as he or she pleases. It even gets
> more complicated because I would like to use a store procedure on
Microsoft
> SQL server.
> Could someone help me creating this store procedure, PLEASE! Thank you.
>|||> SELECT * FROM #Test WHERE col LIKE '%sa%'
This won't work. Aquery like this will also return food name such as 'Carne
Aa' because 'Aa' has 'sa' inside its text, I am only interested on
food that has any of its words *start* with the string 'sa'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Rene
> See if this helps
> CREATE TABLE #Test
> (
> col VARCHAR(50)
> )
> INSERT INTO #Test VALUES ('Salt with water')
> INSERT INTO #Test VALUES ('Chips and salsa')
> INSERT INTO #Test VALUES ('Fried Chicken')
> INSERT INTO #Test VALUES ('Guacamole')
> SELECT * FROM #Test WHERE col LIKE '%sa%'
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> problem
> Microsoft
>|||WHERE col LIKE '% sa%' or col like LIKE '%,sa%' or col like LIKE '%.sa%' or
left(col, 2) = 'sa'
You know where I'm going with this, right.
I have seen a script (stored procedure or function) that extracts words from
a string and converts it to a table, one word per column
This may help you with this problem. Maybe someone here could point you to
the script. I can't seem to be able to find it right now.
"Rene" <nospam@.nospam.com> wrote in message
news:ONj7qTqaFHA.2736@.TK2MSFTNGP12.phx.gbl...
> This won't work. Aquery like this will also return food name such as
> 'Carne Aa' because 'Aa' has 'sa' inside its text, I am only
> interested on food that has any of its words *start* with the string 'sa'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
>