Hi!
I have these tables:
DB1
MID
IIN
Title
FullName
Address
Apt
City
Province
PostalCode
DB2
MID
Lang
Version
How can I output a text file formatted like the one below?
(05) 01046 (F 05)
Mr. Sylvain Cote
123 Sesame Street
Apt 6
Charny, BC
L8T 5G6
where
(05)-MID
01046-IIN
(F 05) - Lang
$3.99/yr .COM!
http://www.greatdomains4less.com
Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.
I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -
I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.
I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?
Thanks
I am trying to print mailing labels and suppress optional address lines to eliminate white space while maintaining label alignment. This is what I am trying but it does not work.
I create a function that determines the length of a field (i.e. street) and increments a counter if the length is zero. It also takes a second parameter that detremines whether to reset the counter. I then use the function in the visibility each row of the label table. Example iif(Code.LineLen(Fields!Street.Value)=0,True,False). I place this on the visibility of each row except the last as that is City,State,Zip and is required. On this last row the expession I use is
=Fields!city.Value & ", " & Fields!state.Value & " " & Fields!zip.Value & iif(Code.LineCount<5,StrDup(5-Code.LineCount,vbCRLF),""). What this is intended to do is print a carraige return and line feed for every row that did not print. Instead no line feeds occur. I have verified that the Code.LineCount is indeed calculating correctly (I printed the value). I've removed the conditional to make sure it has no issues but again nothing. For clarification the code block I am using is this.
Public LineCount as Integer=0
Function LineLen(byval LineIn as string,byval IsFirst as boolean) as Integer
if IsFirst=True then
LineCount=1
end if
If len(LineIn)>0 then
LineCount=LineCount+1
end if
LineLen=Len(LineIn)
end Function
So if anyone either knows why the line feeds do not occur or a better way to handle this please let me know. Thanks.
I have a sheet of mailing labels in which the first three rows have been used. I want a parameter or something when running an SSRS label report where I can specify what row to begin printing on and how many rows I want to print. Is that possible?
View 8 Replies View RelatedI have 6 separate mailing label like reports with a textbox inside a cell. I tried to join them into one report with subreports"but" it does not work since you need the print layout in order to get both columns to show. What is the best way to accomplish this task?
View 5 Replies View RelatedHi, is it possible to send an email from MS SQL? I need to have a trigger which will activate the sending an email. Thanks
View 4 Replies View RelatedHi All, I need to send mails from Database side means.... from stored procedure i need send mails, i will be giving from mailid, subject, body as a parameters and i will be getting to mailids from tables and then i need to send a mail. Please help, if any faced this. Thanks in Advance..........Regards,Mulukutls
View 6 Replies View RelatedHi AllWe are running SQL Server & Outlook with an exchange server. We arelooking for a way to import the address book & email details into adatabase table.We have managed to do this with MS Access by using the import optionexchange(). Is there a similar way this can be done in SQLServer 2000
View 2 Replies View RelatedHi,
I am trying to set up the SQL Server Agent so that when ever a error occurs
the DBA is notified.I created a operator name and when i tested to make sure
it was working it was giving me an error.The error is
Error 22022: SQLServerAgent mail session is not working; check the mail
profile and/or the SQLServerAgent service startup account in the SQLServerAgent properties dialog.
I am new to SQLServer and would like to know how to get this working.
Any help will be appreciated.
Thanks,
Rann
Hi friends,
Any idea about mass mailing system using SQL Server .Pls get back to me.
thanx and regards
Chinmay
Hi folks,
I'm building a DTS package which needs to mail a list of users nightly
The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.
I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.
The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3....etc).
I'm a bit unsure on how to go about doing / writing this.
Can anyone help?
Thanks,
Dave
In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet)
How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set?
Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class...
Imports ADODB
Imports System
Imports System.Xml
Imports System.Collections
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.DtsClient
Public Class ScriptMain
Public Sub Main()
Dim ds As DataSet
Dim dr As DataRow
Dim str As String
Dim dtm As String
Dim s As String
Try
ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Hi, i am a newbie to reporting services.
Am trying to create a mailing label report so that it can pull out the details from the database and gets printed onto the label stickers correctly. However, no matter how i adjust the sizes of the report from the page properties, it's still not printed properly.
I have 7 label stickers on 1 mailing page, so let's say i have 13 records, that will be 2 mailing pages. Each label sticker is about 9cm width by 3.6 cm height. The gap between each label sticker is 0.2 cm
Can anyone advise me on how to go about doing it? By the way, i am using SSRS 2000.
Any help is appreciated. Thanks :)
i am once again working with mailing labels. I have designed the report in SQL Server Reporting Services to print in 3 columns with a body size of 2.625" wide by 1.00" high; a rectangle the same size which then contains a list the same size which contains the data fields. I get fairly good results when I print to one printer however, there does seem to be a bit of "creep" on the page. The problem really shows when trying to print to another network printer where when a user prints the report (labels) by the end of the page data is printing outside of the label margins. are there some properties that I'm missing that would prevent this or could this be related to printer settings?
View 4 Replies View RelatedHi!
I have these tables:
DB1
MID
IIN
Title
FullName
Address
Apt
City
Province
PostalCode
DB2
MID
Lang
Version
How can I output a text file formatted like the one below?
(05) 01046 (F 05)
Mr. Sylvain Cote
123 Sesame Street
Apt 6
Charny, BC
L8T 5G6
where
(05)-MID
01046-IIN
(F 05) - Lang
Hello
I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.
Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.
Thanks
I am trying to use the time series algorithm to predict responses to promotion mailings for subscription renewals. The problem i am having is that response is largely influenced by the number of mailings that are sent out. Can anyone give me any ideas on how i can structure the dataset so that it would take into account how many promotions were sent out? any help would be greatly appreciated.
Frank
I have a requirement wherein PDF files are being rendered from an .rdl (report definition language), through the use of a SSRS scheduler automatically. The generated PDF file is further emailed to a mailing list, through the same scheduler. However, there are situations where the PDF is generated as an empty file (under certain specified circumstances) [through the automatic scheduler run]. In this situation, it is required not to email the PDF at all.
I would appreciate an input which lets me know how to prevent the generation of the PDF file, when there are no records in the dataset that binds to the .rdl.Alternatively, is there any indicator via which the scheduler can be alerted NOT to pick up files 0 KB in size?
Dear fellows,
LOP_BEGIN_XACT, LOP_COMMIT_XACT,
LOP_INSERT_ROWS
Labels as the ones are easy recognizable when you explore LDFS by mean DBCC or whatever but on the contrary the following ones are tricky to reach the meaning:
LOP_IDENTITY_TYPE
LOP_DELTA_SYSIND
LOP_SET_BITS
Does anyone have any link related with this? I tried hard to search by Goog or something like that but unsuccessfully at all.
I have a simple bar graph. All I am looking to do is display the values on each bar. Can someone please help me. Thanks
View 1 Replies View Related
I have a page that is part of a 5 page wizard. The wizard gathers data for a claim filter. The second page is laid out with four labels at the top
Filter Name: <filterName> -- filterName_LFilter Description: <filterDescription> -- filterDescription_LFilter Data Source: <filterDataSource> -- filterDataSource_LFilter Purpose: <filterPurpose> -- filterPurpose_L
--I have written an sp to populate these four label.Text values. I want to populate them on page load.
Two questions. How do I get four output values out of the sp? Is there a better way to do this than an sp?
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
-- =============================================-- Author: <Author,,Name>-- ALTER date: <ALTER Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[a_spNewFilter_Step1_Summary] -- Add the parameters for the stored procedure here @filterID int ,
@filtername varchar(100) OUTPUT, @filterdescription varchar(250) OUTPUT, @filterOwnerID int OUTPUT, @filterDataSourceID INT OUTPUT, @filterPurposeID int OUTPUT
ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;
--DECLARE @FilterID int
--SET @filterID = 14
SELECT filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName + ' ' + das.AuditorLastName as FilterOwner, dfds.filterDataSource, dfp.filterPurposeFROM a_factSamplingFilter_2 fsfINNER JOIN dbo.a_dimFilterDataSource dfdsON dfds.filterDataSourceID = fsf.filterDataSourceIDINNER JOIN dbo.a_dimAuditStaff dasON das.AuditorID = fsf.filterOwnerIDINNER JOIN dbo.a_dimfilterPurpose dfpON dfp.filterPurposeID = fsf.filterPurposeIDWHERE filterID = @filterIDGROUP BY filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName, das.AuditorLastName, dfds.filterDataSource, dfp.filterPurpose
END
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
----
protected void Page_Load(object sender, EventArgs e) {
int filterID = int.Parse(Request.QueryString["filterID"]); /// grab the filterID from the URL query string
string connectionString = WebConfigurationManager.ConnectionStrings["DDT"].ConnectionString; SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("a_spNewFilter_Step1_Summary", con); // get the values from filter setup step1 cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FilterID", SqlDbType.Int)); // using the filterID created in step1 cmd.Parameters["@FilterID"].Value = filterID;
cmd.Parameters.Add(new SqlParameter("@FilterName", SqlDbType.VarChar, 100)); cmd.Parameters["@FilterName"].Direction = FilterName_TB.Text;
cmd.Parameters.Add(new SqlParameter("@FilterDescription", SqlDbType.VarChar, 255)); cmd.Parameters["@FilterDescription"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterOwner", SqlDbType.VarChar,50)); cmd.Parameters["@FilterOwner"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterDataSource", SqlDbType.VarChar,50)); cmd.Parameters["@FilterDataSource"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterPurpose", SqlDbType.VarChar,100)); cmd.Parameters["@FilterPurpose"].Direction = ParameterDirection.Output;
con.Open();
try { cmd.ExecuteNonQuery(); } finally { con.Close(); }
///get name, description, owner, datasource, purpose
}
Hi,
We are having trouble getting labels to print correctly when there is a lot of data close together on the labels. These are more than just address labels, so there are many lines, graphics, and borders. Are there additional tools or suggestions for keeping labels consistant?
Thanks
I have a pie chart and have tried every option I could find on the Data tab for the point labels. They bunch up all over the place. What am I missing?
Does Dundas do this better?
Jerry
Is there a SQL statement that I can use which allows me to set value lables in a result set? For example, if a table has a gender variable with values of 'M' & 'F', can I write a sql statement which creates a result set that changes these to 'Male' & 'Female' so that everything I do with the result set shows the new labels (but does not change the underlying M/F values in the orginal table?
I do not want to create a new table, but rather display the M/F values as Male/Female when I access the result set in an application (such as Report Services).
Thanks for any advice you can give.
Ed Sloat
I have a line chart that has several series, and it routinely leaves off 1 or 2 point labels on the last X-axis.
Is anyone else experiencing this? If so, does anyone know what the cause is, and is there a fix?
Thanks!
BobP
Hi..
Just want to ask if anyone has encountered the same problem..
I have used the Simple Bar Graph to display the list of employees with the most number of emails/chats and sorted it so that the highest number of emails will appear at the bottom of the graph. The problem is that the graph doesnt display a label on the highest number of email/chats. It displays the number of email/chats that the other employee has and its only the employee with the highest number of email/chats that has no number label. It just wont show and I have no idea now on what to do about it.
Does anybody have a solution for this one?
hi there, i have a payment page which uses javascript to calculate payments and what remains etc. That all works fine, the problem i have is that when you are finished and you go to the next page it should save all of the records to the order table where the order_id = a querystring. Now i have tried using an insert method and an update method, not really sure whats the difference when putting data into an existing record. What happens though is that i can save the contents of labels etc but it wont save the content of dropdown lists (of which there are 2) i just cant work it out, i have posted my code below
string strOrderID = Convert.ToString(Request.QueryString["OrderID"]);
string strDiscountPercent = Convert.ToString(5656);double dblDiscountMoney = Convert.ToDouble(txtDiscountMoney.Text);
string strPaymentMethod1 = ddlPaymentMethod1.SelectedValue;double dblPaymentAmount1 = Convert.ToDouble(txtPaymentAmount1.Text);
string strPaymentRecipt1 = txtPaymentRecipt1.Text;string strPaymentMethod2 = ddlPaymentMethod2.SelectedValue;
double dblPaymentAmount2 = Convert.ToDouble(txtPaymentAmount2.Text);string strPaymentRecipt2 = txtPaymentRecipt2.Text;
string sConnectionStringCustInfo = "my connection string";
SqlConnection objConnOID = new SqlConnection(sConnectionStringCustInfo);
//This is the sql statement.using (objConnOID)
{
objConnOID.Open();
string sqlUpDate = "UPDATE tbl_order SET discount_Percent = " + txtDiscountPercent.Text +
", discount_money = " + dblDiscountMoney + ", payment_method1 = " + strPaymentMethod1 + =========== this is the problem line here!
", payment_amount1 = " + dblPaymentAmount1 + ", payment_ref1 = " + strPaymentRecipt1 +
", payment_amount2 = " + dblPaymentAmount2 +", payment_ref2 = " + strPaymentRecipt2 +
"WHERE order_ID = " + strOrderID;
SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConnOID);
try
{
objCmd1.ExecuteNonQuery();
}
and because it throws an error it wont update the database with any other record. does anyone have any ideas, i have spent hours last night trying to figure this one out and im sure its simple! but i need some help!
Cheers
Jez
i have an sqldatasource that has a 1 row dataset
how can i read these values to labels?
thanks
I addressed this situation in another thread, but since the thread had not been updated for some time I felt a new thread may have been warranted to included uptodate information and requests for further updates.
I will refresh the topic's primary issue:
When using a matrix, a developer wanted to provide column headers atop the columns to help users distinquish between multiple columns of the same relative data type(columns of digits, etc.)
The space in the upper left corner of the matrix will create a void that cannot be broken down or divided so that a column header label can be applied. It's either one label for the whole void or nothing at all.
VOID
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
A poster suggested placing a rectangle inside the voided area above.
Rectangle
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
Then placing a series of textboxes within the rectangle area aligned to the width of the row columns
Rectangle
Upper Group Column
rectangle txtbox
rectangle txtbox
rectangle txtbox
rectangle txtbox
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
The problem is that when the report is run and rendered the rectangle txboxes are no longer aligned at the bottom. Even though the text within the textboxes is set to the bottom.
Instead the rendered version appears like this.
rectangle txtbox
rectangle txtbox
rectangle txtbox
rectangle txtbox
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
Thus rather than having the labels inside the rectangle textboxes aligned at the bottom so that they appear more in-line with their assigned columns, they are automatically top-justified and this makes the labels look awkward.
So does anyone have any suggestions on how I can get the rectangle textboxes to be aligned adjacent to the tops of the row columns so that their labels appear in-line with their appropriate columns?
Thanks in advance.
I have a horizontal barchart with integer values, both positive and negative. Major gridlines are shown and I have not specified the interval or the label format. Sometimes the gridline labels (Y axis) show decimal values. Is there anyway to format these? I have tried to format the labels as "#0" but then I see labels occuring twice. I have also played with the intervals, but then sometimes, depending on the values, the zero line is not being shown.
Does anybody have an idea?
Thanks in advance!
Joos
Hi People,
I'm with a new problem.
I creted a simple bar type and sometimes the values of point labels are showing inside of bar, This is a problema, because if the length of bar will be short, the value doesn't shows. How can I make to force the point label value outside of bar ??
Thanks
I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?
I currently create crosstabs with case statements as follows:
select
sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db
I figured you could create some sort of while loop for the case statements but I can't get the syntax right.
Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?