Automatically print multiple copies of the same report with different label on letter size p

Hi,

I am new to reporting services and I'm really stuck on a design problem. Can someone please help me?

I would like to design my own print function. When a user clicks on the print icon (preferably the one that came with reporting services), the report is automatically printed twice, once with "For Person A" and the second time with "For Person B" on it. It doesn't matter where these two labels are placed on the page. These two reports need to be printed on letter-size paper regardless of user's selection. How do I do this with minimum amount of code?

Any help would be greatly appreciated!

Depending on how the names of person A and person B actually get onto the report, you could add a grouping by person with a page break and actually make the report have 2 pages, 1 per person.|||

Hi

Thanks for your help.

The names are constant - ie they can be hardcoded.

If I make the report 1 page per person, does that mean the user will see two copies of the report in preview? Is there a way so that the user sees one copy but can print multiple copies with a single click?

Thanks!

|||

Hi

If there is a field with "Person A" and "Person B" you can add
a grouping by that field and set 'Page Break at end' on the grouping to true.

If this is a static value that has to be hard coded I cannot think of anything.

Problem when using the group by person approach is that the report will display
twice in the browser, each seperated by a pagebreak.

G

|||More specifically, the report will indicate that it has multiple pages (hence allowing the user to browse to the next page) but by default only the first page will show. When you export or print, all pages will be outputted.|||

Is there a way to do this without showing multiple copies (show one copy of the report in preview but prints two)?

Ideally I would like to create a print function that can be used across all reports, instead of changing all existing reports.

Thanks

|||

In that case your only other option is write your own rendering extension. That's quite involved and probably not how far you would want to go in this case.

You can't override the default print function.

|||Ok, got it. Thanks for your help :)

Automatically grow file did not function.

My server SQL7.0 SP4 found the problem Automatically grow file did not function.
I try to manaully input to Space Allocated (MB) but I receive error as below
fcb::ZeroFile(): GetOverLappedResult() failed with error 121.This error was associated with disk problems in the past. I'd try to verify
the interity of the disk.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Jittima D." <anonymous@.discussions.microsoft.com> wrote in message
news:F310CAB1-E3B8-48A0-B90A-28D5C3273DEE@.microsoft.com...
> My server SQL7.0 SP4 found the problem Automatically grow file did not
function.
> I try to manaully input to Space Allocated (MB) but I receive error as
below
> fcb::ZeroFile(): GetOverLappedResult() failed with error 121.
>

Automatic text completion

I'm not entirely sure this is the place for it but I need to implement an automatic text completion function.

I'd like to know if there is something, such as a built-in function, that could help me.
The best idea I have is to create some sort of node tree and work with that but there has to be a better way.

I need to do it in C# or in MS SQL. Any kind of help is much appreciated!
YOu should either write your request to a platform group, e.g. WIndows Applications with C# or Web.Applications with C#, this mainly has nothing do to with SQL Server, more with the frontend you will create.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Actually I implemented it with SQL and it works rather well I must admit.
It returns:
"Emma Strandberg"
Since there is a Firstname Emma and her last name is Strandberg

SQL Code

DECLARE @.match varchar(15)

SET @.match = 'Em'

/* SET NOCOUNT ON */

SELECT (Firstname + ' ' + Lastname) AS Wholename
FROM CMR_Arrivals
WHERE SUBSTRING(Lastname, 1, LEN(@.match)) = @.match
OR SUBSTRING(Firstname, 1, LEN(@.match)) = @.match

|||

And it will also return 'Bill Emerson'

However, you asked about

Automatic text completion

. Your 'solution' has absolutely nothing to do with auto-completion.

Yes, your search 'kinda' works to find the data you seek. One consideration is that by wrapping the column with a function, substring(), you guarantee that the process will NOT be able to efficiently use indexing. On a large table, that can be a bit slow.

You may wish to refer to Books Online about the use of Wildcards in search criteria.

See Books Online, Topics:

Wildcards LIKE 'Pattern Matching in Search Conditions'|||

The query will be used with AJAX AutoComplete(Extender Toolkit) and the results will be limited hence the returned value wont be so many.

You are right in that it's not exactly what I was originally looking for!

Peter Ritchie (MVP) suggested:

" Sounds like what you want is AutoComplete, e.g. TextBox.AutoCompleteCustomSource.

Ken Getz has a good overview of the feature here: http://code-magazine.com/Article.aspx?quickid=0509111"

Where I replied:

" Sounds like something I was looking for BUT I was going to use the AutoCompleteExtender in the ASP.NET AJAX Toolkit.

Now I don't mind skipping the AJAX control but when looking at a Textbox the only option I have is AutoCompleteType.

None of the other functions such as AutoCompleteCustomSource are available.

I have .Net 2.0 installed and does say TextBox 2.0.0.0 .Net when holding the mouse over the control in VS2005.

Am I missing something here?
"

|||

Typically, he way 'autocomplete' functionality works is that a set of data has been retrieved from the database, as as the user types into the textbox control, the textchanged event is used to update the filter on the data.

Alternatively, on each keystroke, the textchanged event could be used to re-run the query to the data server.

BUT that is a very bad idea in terms of system performance.

Automatic text completion

I'm not entirely sure this is the place for it but I need to implement an automatic text completion function.

I'd like to know if there is something, such as a built-in function, that could help me.
The best idea I have is to create some sort of node tree and work with that but there has to be a better way.

I need to do it in C# or in MS SQL. Any kind of help is much appreciated!
YOu should either write your request to a platform group, e.g. WIndows Applications with C# or Web.Applications with C#, this mainly has nothing do to with SQL Server, more with the frontend you will create.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Actually I implemented it with SQL and it works rather well I must admit.
It returns:
"Emma Strandberg"
Since there is a Firstname Emma and her last name is Strandberg

SQL Code

DECLARE @.match varchar(15)

SET @.match = 'Em'

/* SET NOCOUNT ON */

SELECT (Firstname + ' ' + Lastname) AS Wholename
FROM CMR_Arrivals
WHERE SUBSTRING(Lastname, 1, LEN(@.match)) = @.match
OR SUBSTRING(Firstname, 1, LEN(@.match)) = @.match

|||

And it will also return 'Bill Emerson'

However, you asked about

Automatic text completion

. Your 'solution' has absolutely nothing to do with auto-completion.

Yes, your search 'kinda' works to find the data you seek. One consideration is that by wrapping the column with a function, substring(), you guarantee that the process will NOT be able to efficiently use indexing. On a large table, that can be a bit slow.

You may wish to refer to Books Online about the use of Wildcards in search criteria.

See Books Online, Topics:

Wildcards LIKE 'Pattern Matching in Search Conditions'|||

The query will be used with AJAX AutoComplete(Extender Toolkit) and the results will be limited hence the returned value wont be so many.

You are right in that it's not exactly what I was originally looking for!

Peter Ritchie (MVP) suggested:

" Sounds like what you want is AutoComplete, e.g. TextBox.AutoCompleteCustomSource.

Ken Getz has a good overview of the feature here: http://code-magazine.com/Article.aspx?quickid=0509111"

Where I replied:

" Sounds like something I was looking for BUT I was going to use the AutoCompleteExtender in the ASP.NET AJAX Toolkit.

Now I don't mind skipping the AJAX control but when looking at a Textbox the only option I have is AutoCompleteType.

None of the other functions such as AutoCompleteCustomSource are available.

I have .Net 2.0 installed and does say TextBox 2.0.0.0 .Net when holding the mouse over the control in VS2005.

Am I missing something here?
"

|||

Typically, he way 'autocomplete' functionality works is that a set of data has been retrieved from the database, as as the user types into the textbox control, the textchanged event is used to update the filter on the data.

Alternatively, on each keystroke, the textchanged event could be used to re-run the query to the data server.

BUT that is a very bad idea in terms of system performance.

Automated testing | How to "force" GETDATE() function to return specific value?

Hello,

Our QA team have running a lot of test scripts (for automated regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.

Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).

It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_DATE init parameter.

Is it possible to 'change' the current date/time on 'database' level,
instead of on OS level for MSSQL2000?
Do you know other means to do such things?

Thanks in advance,

Konstantin"Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> Hello,
> Our QA team have running a lot of test scripts (for automated regression
> testing), they run them on the different databases (Oracle/MS SQL).
> Several of those tests are dependent on the current date/time. In order to
> be
> able to use them efficiently, we changed the current date/time on the QA
> database server to a specific date/time before starting the scripts, so we
> are sure the test scripts always run in the same environment.
> Resetting the date/time of the database server gives us more and more
> problems (OS problems, backup/ virusscan, ...).
> It is possible to fix the problem with SYSDATE function on Oracle by
setting
> FIXED_DATE init parameter.
>
> Is it possible to 'change' the current date/time on 'database' level,
> instead of on OS level for MSSQL2000?
> Do you know other means to do such things?
> Thanks in advance,
> Konstantin

I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
return the same value every time it's called? If so, I don't believe there's
any way to do this in SQL Server. You could create your own function called
dbo.getdate(), and use that, but then it wouldn't be a valid test of your
'real' code.

In any case, it's not clear why you would want to always test with the same
datetime value - you wouldn't prove anything except that your code works
with one particular datetime, and that probably isn't desirable. It might be
better to put some work into establishing what the test results should be
for a given datetime input value, and validate your code that way.

If I've misunderstood, or if this doesn't help, perhaps you can clarify
exactly what you want getdate() to return, and how you want to use it.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fc25218$1_3@.news.bluewin.ch...
> "Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
> news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> > Hello,
> > Our QA team have running a lot of test scripts (for automated regression
> > testing), they run them on the different databases (Oracle/MS SQL).
> > Several of those tests are dependent on the current date/time. In order
to
> > be
> > able to use them efficiently, we changed the current date/time on the QA
> > database server to a specific date/time before starting the scripts, so
we
> > are sure the test scripts always run in the same environment.
> > Resetting the date/time of the database server gives us more and more
> > problems (OS problems, backup/ virusscan, ...).
> > It is possible to fix the problem with SYSDATE function on Oracle by
> setting
> > FIXED_DATE init parameter.
> > Is it possible to 'change' the current date/time on 'database' level,
> > instead of on OS level for MSSQL2000?
> > Do you know other means to do such things?
> > Thanks in advance,
> > Konstantin
> I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
> return the same value every time it's called? If so, I don't believe
there's
> any way to do this in SQL Server. You could create your own function
called
> dbo.getdate(), and use that, but then it wouldn't be a valid test of your
> 'real' code.
> In any case, it's not clear why you would want to always test with the
same
> datetime value - you wouldn't prove anything except that your code works
> with one particular datetime, and that probably isn't desirable. It might
be
> better to put some work into establishing what the test results should be
> for a given datetime input value, and validate your code that way.
> If I've misunderstood, or if this doesn't help, perhaps you can clarify
> exactly what you want getdate() to return, and how you want to use it.
> Simon

Thank you for replay.

Yes. You are right. The FIXED_DATE lets you set a constant date that SYSDATE
will always return instead of the current date.

The main problem is that if you take a fixed test database, and run for
example a report, the output of that report can be (very) different if you
run this report today vs. the run you will do tomorrow vs. ... Other typical
example: our application (financial one) reacts differently if you want to
create some entities with dates in the past (different execution flow).

We are using Rational Robot in the automated testing. It validates that all
system reaction and outcome is identical to the reference run (verification
points). If the 'current date/time' is changed since the 'reference' run,
you get a lot of (unnecessary) errors/warnings and failed verification
points. That's why we need to run test scripts with specific datetime value.

Of course, automated tests are not intended to replace all other tests,
their purpose to supplement another tests. Automated test scripts are mainly
used for regression testing. Since it is very difficult and time-consuming
to make Robot test scripts time independent it is acceptable for us to
execute tests with fixed datetime.

Unfortunately, calling of new dbo.getdate() function will cause modification
of application. It is time-consuming and task. That's why our main goal is
to solve the problem by means of Oracle/MSSQL parameters. Moreover, your
solution with dbo.getdate() will not work in a case when we need correct
datetime (not fixed).

CREATE FUNCTION dbo.getdate()
RETURNS datetime AS
BEGIN
-- return cast ('2003-01-01' as datetime) -- It is OK.
return getdate() -- It is NOT OK.
END

The GETDATE() is a nondeterministic function and it is not allowed to use it
in user-defined functions.

If we will not solve the problem by configuring MSSQL database (I think it
is most likely) our solution will be to create the following view:

CREATE VIEW v_nondeterministic AS SELECT getdate() AS getdate;

And to use SELECT getdate FROM v_nondeterministic where required.

Best regards,

Konstantin

Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

AutoGenerate No. in SQL Server 2000

Hi
I would like to create an Autogenerate function which has to do the following

autogenerate field type is varchar(10). in that first 2 characters are purely character string. remaining will be numbers

i'll pass the following parameters into the function
1. tablename
2. columnname
3. 2 character string that has to build the first 2 characters
eg: functionname(emp, empid, 'EM')

Here the function has to execute and return the generated no.
eg: EM1 - IF RECORDS NOT AVAIL IN THE TABLE
EM5 - IF ALREADY RECORDS ARE AVAIL &THE MAX RECORD NO IS EM4.

In this functioin i've to pass any tablename and corresponding field with the 2 character build string... Already i tried. Few problems are there in passing the tablename as parameter...

Anybody help me in that...

Thanks in advance...Hi,

What were the problems you got?

Madhivanan|||Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||Hi,

Inside the function write something like this

Declare @.AutoGen varchar(10)

if (select count(*) from @.t) =0
select @.AutoGen = 'PT1'
else
select @.AutoGen = 'PT'+convert(varchar(8),max(right(id,len(id)-2))+1) from tableName

Madhivanan|||Hi,
Actually i've got a problem to passing the Tablename as parameter.
Its not working... U try to create a real function in SQL server 2000 with
a table, test it, then... give me reply. then only u can get what'll be the problem...

Tx in advance...|||You will need to use dynamic SQL to do this by building your SQL Statement as a string and then EXECuting it.

That said, do NOT do this. The way you are generating these codes goes against good principles of design, and my crystal ball tells me that this will be an endless source of trouble.

I strongly urge you to reconsider your design.|||Hi...

Anybody got the Autogenerate Nos. Function in SQL Server 2000.

Already i mentioned the problems in the same thread...

Help me

Tx in Advance...|||Easy to do! Without your data I can't test this, but something as simple as:CREATE PROCEDURE fubar
@.pcTable sysname
, @.pcColumn sysname
, @.pcPrefix CHAR(2)
, @.pcResult CHAR(10) OUTPUT
AS

if 'foo' = @.pcTable AND 'bar' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(foo.bar, 3, 8))
FROM foo) + 1, 8)), '00000001')
else if 'baz' = @.pcTable and 'bat' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(baz.bat, 3, 8))
FROM baz) + 1, 8)), '00000001')
else SELECT @.pcResult = Repeat('?', 10)

RETURN-PatP|||Sorry...

In ur procedure why u checked the table name. I don't want to check the table name and column names. i'll just pass the table and column names with build string (2 character string). Thats all. The function has to return a no from the specific given table. (if rows are not avail) --> 'PT1'

if rows avail--> 'PT5' (for example already the maximum value is PT4).

It should be for all table... I've no. of tables. For each and every table i can not check it like ur procedure coding... got it...?

Tx... Reconsider it and give the better solution...|||You're trying to do something that SQL doesn't do well because it has other features that work SO much better. I'm sorry you don't like my solution, even though it does exactly what you asked for and relatively efficiently at that.

Maybe someone else has a better answer. Personally, I'd just suggest that you simply use a better solution such as an IDENTITY column that would sidestep the need for this nonsense.

-PatP|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from tableName

Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from @.tableName

here @.tablename is the parameter. if i give like this its giving an error...
please try to do the function, check it and give me the reply.

Tx in advance...|||Hi...

Anybody is having good solution for my question regarding this autogenerate nos. in SQL server 2000. Question is avail in this same thread...

Good suggestions and Good & Efficient Solutions are welcome

Tx in advance.|||PatP gave you a good & efficient solution. If you don't understand, it is not due to not having the right solution.