Multi Page Report

Hi All,
I have two tables in a SQL 2000 database. One table contains a few text
fields and the other contains any overflow data from the original table.
I am required to print a one page report with the data from the first table
and then if there are any data in the overflow table, to automatically print
a second page containing the data from the second table.
Page 2 of the report doesn't look anything like page 1. It's layout is
different and it only contains the data from the second table.
These text fields (in the overflow) may be a max of 10K.
I'm new to RS and this requirement has been beating me up.
Any ideas?
Thanks.Two options. You can use either multiple datasets and put a page break
between them or you can use a sub report. Again with a page break between
them.
Bruce L-C
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> Hi All,
> I have two tables in a SQL 2000 database. One table contains a few text
> fields and the other contains any overflow data from the original table.
> I am required to print a one page report with the data from the first
> table
> and then if there are any data in the overflow table, to automatically
> print
> a second page containing the data from the second table.
> Page 2 of the report doesn't look anything like page 1. It's layout is
> different and it only contains the data from the second table.
> These text fields (in the overflow) may be a max of 10K.
> I'm new to RS and this requirement has been beating me up.
> Any ideas?
> Thanks.|||Thanks.
We actually started looking at the Sub-Report option.
Thanks for your help.
"Bruce Loehle-Conger" wrote:
> Two options. You can use either multiple datasets and put a page break
> between them or you can use a sub report. Again with a page break between
> them.
> Bruce L-C
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> > Hi All,
> >
> > I have two tables in a SQL 2000 database. One table contains a few text
> > fields and the other contains any overflow data from the original table.
> >
> > I am required to print a one page report with the data from the first
> > table
> > and then if there are any data in the overflow table, to automatically
> > print
> > a second page containing the data from the second table.
> >
> > Page 2 of the report doesn't look anything like page 1. It's layout is
> > different and it only contains the data from the second table.
> >
> > These text fields (in the overflow) may be a max of 10K.
> >
> > I'm new to RS and this requirement has been beating me up.
> >
> > Any ideas?
> >
> > Thanks.
>
>

Multi Language Report Function

I have written a function which translates text based upon the language the
user chooses to see the report in (it can also be passed in as a data driven
parm for static runs) Anyway ... my question is this ... since I am new to RS
and new to Vb .Net I was wondering if someone out there can look at what I am
trying to do and suggest a better way ... since I dont want to have to code a
function like this for 25+ fields to be translated and in 4+ languages. I
think when you see the function I wrote you will get the idea and perhaps be
able to suggest an efficient way to translate all fields at once or perhaps
"generic" function (which I tried to code too with nested CASE statements but
abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
This is what I code in the text box for the expression ===>
=Code.TranslateTitle(Parameters!pm_language_code.value)
HERE IS THE FUNCTION
Function TranslateTitle ( ByVal Language_Code As String ) As String
Select Language_Code
Case "EN"
Return "Detailed Report : "
Case "FR"
Return "Rapport Détaillé : "
Case "DE"
Return "Ausführlicher Report : "
Case "NL"
Return "Gedetailleerd Rapport : "
Case Else
Return "Detailed Report : "
End Select
End Function
This works just fine ... but I have 25+ fields with translations !!!
Thanks in advanceHi,
You can use resource files just as in ASP.NET applications.
You can reference these resource dll files within a function written as a
custom code.
Eralper
http://www.kodyaz.com
"MJ Taft" wrote:
> I have written a function which translates text based upon the language the
> user chooses to see the report in (it can also be passed in as a data driven
> parm for static runs) Anyway ... my question is this ... since I am new to RS
> and new to Vb .Net I was wondering if someone out there can look at what I am
> trying to do and suggest a better way ... since I dont want to have to code a
> function like this for 25+ fields to be translated and in 4+ languages. I
> think when you see the function I wrote you will get the idea and perhaps be
> able to suggest an efficient way to translate all fields at once or perhaps
> "generic" function (which I tried to code too with nested CASE statements but
> abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
> This is what I code in the text box for the expression ===>
> =Code.TranslateTitle(Parameters!pm_language_code.value)
> HERE IS THE FUNCTION
> Function TranslateTitle ( ByVal Language_Code As String ) As String
> Select Language_Code
> Case "EN"
> Return "Detailed Report : "
> Case "FR"
> Return "Rapport Détaillé : "
> Case "DE"
> Return "Ausführlicher Report : "
> Case "NL"
> Return "Gedetailleerd Rapport : "
> Case Else
> Return "Detailed Report : "
> End Select
> End Function
> This works just fine ... but I have 25+ fields with translations !!!
> Thanks in advance|||Thanks for your response however I dont know asp .net - in fact ... just got
a book to learn it. So I dont know how to do what you are suggesting here.
Could you be more specific? Point to a simple example maybe?
"eralper" wrote:
> Hi,
> You can use resource files just as in ASP.NET applications.
> You can reference these resource dll files within a function written as a
> custom code.
> Eralper
> http://www.kodyaz.com
> "MJ Taft" wrote:
> > I have written a function which translates text based upon the language the
> > user chooses to see the report in (it can also be passed in as a data driven
> > parm for static runs) Anyway ... my question is this ... since I am new to RS
> > and new to Vb .Net I was wondering if someone out there can look at what I am
> > trying to do and suggest a better way ... since I dont want to have to code a
> > function like this for 25+ fields to be translated and in 4+ languages. I
> > think when you see the function I wrote you will get the idea and perhaps be
> > able to suggest an efficient way to translate all fields at once or perhaps
> > "generic" function (which I tried to code too with nested CASE statements but
> > abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
> >
> > This is what I code in the text box for the expression ===>
> >
> > =Code.TranslateTitle(Parameters!pm_language_code.value)
> >
> > HERE IS THE FUNCTION
> >
> > Function TranslateTitle ( ByVal Language_Code As String ) As String
> > Select Language_Code
> > Case "EN"
> > Return "Detailed Report : "
> > Case "FR"
> > Return "Rapport Détaillé : "
> > Case "DE"
> > Return "Ausführlicher Report : "
> > Case "NL"
> > Return "Gedetailleerd Rapport : "
> > Case Else
> > Return "Detailed Report : "
> > End Select
> > End Function
> >
> > This works just fine ... but I have 25+ fields with translations !!!
> >
> > Thanks in advance

Multi Format Text File

Hello. I am in the process of migrating an old app to SQL Server. The old app reads hundreds of different flat file formats. One of the more complex ones is a multi-format delimited file. For example:

01^Bob Johnson^123 Main St^Anytown^St
02^Book1^$20
02^Book2^$30
03^Gift Cert^Happy Birthday^$100

This file is delimited with the ^ character. Note that the first 2 characters identify the row type. All 01 rows have data in the format: Name, Street Address, City, State. All 02 have data in the format: Book name, price. Etc.

Any clever ideas on how to parse this? I tried setting it up as a flat file source with the ^ delimiter. It doesn't work - in this example it wraps the third row to the end of the second row and keeps adding columns to fill out the row.

The only option that I can think of is to pull the entire row into one long column, and then use a script component to manually substring each column out.

Any help would be greatly appreciated.

Thanks,
Chris

tofferr wrote:

The only option that I can think of is to pull the entire row into one long column, and then use a script component to manually substring each column out.

Yes! That's the best thing to do, in my opinion, and what I do in practice. You can also use a derived column to substring the rows instead of a script component.

Also, you could have one data flow with a conditional split in it to direct each record type to a unique flat file, raw file, sql server table, etc... for staging. From there you can have have an appropriately mapped flat file connection (to the staging file/table/whatever) and continue using a data flow as you normally would.

So, bottom line, which would you rather maintain? substrings or flat file definitions?|||It looks like the rows are related. I think the script will need to generate some type of transaction key for every 01 record and carry it forward for the other row types. I don't think the relationship between the rows can be maintained by using the alternate methods.|||

JayH wrote:

It looks like the rows are related. I think the script will need to generate some type of transaction key for every 01 record and carry it forward for the other row types. I don't think the relationship between the rows can be maintained by using the alternate methods.

If each file only contains one name, then you're fine. If if contains more than one name then when loading the initial file you may want to use a script component to generate a unique key which gets incremented by one each time you read a new 01 record. Carry this key forward and you can later reassemble the records accordingly.|||Thank you for the quick responses!

I simplified my example significantly. For example, a 01 row really has well over 150 fields. And, yes, the rows are related and there are many 01 records in each file, so I believe Jay H is correct.and the alternate methods will not work.

Any other helpful thoughts?

BTW, I tried to check out http://www.ssistalk.com/ but it did not load? Temporary problem?

Thanks,
Chris
|||

tofferr wrote:

Any other helpful thoughts?

Yep, see my response above. You can mark each "record" (the 01 and its associated children) with a unique key. Then later when you work with the data you can work on batches as defined by that key.

tofferr wrote:

BTW, I tried to check out http://www.ssistalk.com/ but it did not load? Temporary problem?

Not sure what the deal was there, but the machine was unresponsive. It's back up. Thanks for the note.

Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql

Mulitple stored proc parameters

Hi,
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql

MSSQL to Text File? how? thanks...

As we know, MySQL have function to output its data into Text File using "Select * into outfile 'C:/mytext.txt".

Does MSSQL has "Select into Outfile" function? If yes, what is the function??

Thanks in advance :)

Anderson

Do you have access to the SQL Manager GUI? You can output to a file from that.

You can also useSQLCMD -i Input.sql -o C:\mytext.txt -e from the command prompt.

|||P.S. - I'm not positive, but I don't think you can do it directly from the SQL statement like you can in MySQL.|||

You can use supplied BCP application or SQL Server Management Studio Express.

Example for BCP:

c:\Program Files\Microsoft SQL Server\90\Tools\Binn>bcp RZBWSPR.dbo.MenuItem out Result.txt -T -S DVESIC\SQLEXPRESS -c

|||You can call bcp utility with xp_cmdshell from T-SQL. Take a look at this post:
http://forums.asp.net/thread/1388531.aspx

MSSQL over IIS 5

I'm having troble with MSSQL 2k and IIS 5
a textarea field (text), can't post more then 150 chars, If I try, the text
recorded are something like "üh3", and no sight of my typed text!!
What's hapen? this does not occurs over Sambar or Xitami.The SQL Server will only store what you ask it to store. Suggest running a
SQL Profiler trace to see exactly what is being passed to the SQL Server.
--
HTH
Ryan Waight, MCDBA, MCSE
"Feijó" <feijo@.x.suply.com> wrote in message
news:%23Vh52VmjDHA.1708@.TK2MSFTNGP12.phx.gbl...
> I'm having troble with MSSQL 2k and IIS 5
> a textarea field (text), can't post more then 150 chars, If I try, the
text
> recorded are something like "üh3", and no sight of my typed text!!
> What's hapen? this does not occurs over Sambar or Xitami.
>|||Ok, I will use the profiler to get some hint
the version is 7.0, any bugs on it?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> escreveu na mensagem
news:u08KWamjDHA.2676@.TK2MSFTNGP11.phx.gbl...
> The SQL Server will only store what you ask it to store. Suggest running a
> SQL Profiler trace to see exactly what is being passed to the SQL Server.
> --
> HTH
> Ryan Waight, MCDBA, MCSE|||There were but they've been rectified with Service Packs. Ensure you are
running the latest Service Pack. For SQL 7 it's 7.00.1063, SELECT @.@.VERSION
will show which version you are running.
--
HTH
Ryan Waight, MCDBA, MCSE
"Feijó" <feijo@.x.suply.com> wrote in message
news:OFX1alojDHA.1656@.tk2msftngp13.phx.gbl...
> Ok, I will use the profiler to get some hint
> the version is 7.0, any bugs on it?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> escreveu na mensagem
> news:u08KWamjDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > The SQL Server will only store what you ask it to store. Suggest running
a
> > SQL Profiler trace to see exactly what is being passed to the SQL
Server.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
>