extract information from tables

Hello Guys,

my collegue left the job and I have been chosen to work on his assignments.

He posted this question before and got the answer but I couldn't find a way to implement it in the program.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1726638&SiteID=1

I changed the database a bit as I couldn't understand his logic of putting both PayID and TransNo as this would be a linked table and should have many to many relationship

Code Snippet

TransactionID int Unchecked
TransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked
TransPayID (from TransPayLnk Table as foreign key)

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked
TransPayID (Foreign Key from Tbl_TransPayLnk)

My requirement would stay the same, I would need to put the data from these 3 tables into a single line without duplicating the records and I would need to display them in a DataGridView.

P.S: Would it be possible to have Mixed when a TransPayID is matched in Tbl_payment with more then one record rather then brining cash and cheque.

Thank you all for your help.

Bye

Gemma

The answer is already available on the given post itself. What exactly you are looking for?|||

Yes the answer is there but I changed the table structure and though I appreciate the effort of DaleJ but I can't seem to display it like this in a DataGridView Row.
Why don't you check the new table structure and please reply on this.

Thanks for your help

Gemma

Expressions in RS..

Hey guys,
I try to hide some sections of my report using the expression editor.
I tryed to find what language the expression editor is but I did not found
it.
Does someone knows any references about expressions in RS?
Thanks!VB.Net. I sometimes open up a VB project to test out expressions before
using them in RS. Same with code behind reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:uJ37Cv$eFHA.580@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I try to hide some sections of my report using the expression editor.
> I tryed to find what language the expression editor is but I did not found
> it.
> Does someone knows any references about expressions in RS?
> Thanks!
>

Exporting to multiple sheets in Excel has limitations ?

guys, I am exporting a report in excel that generates in different
sheets.
It seems that only 3 tab or sheets can be generated.
If the report exceeds 3 sheets all the rest of the sheets combined in
the last sheet.
Can anyone confirm this? Does it have any work around or I am just
doing something wrong?
thanks in advanceHi, I am doing some testing on sending data to Excel myself. I just tested
this and was able to make 5 sheets when I made page brakes between rectagles
in the report. Have you used the device setting RemoveSpace when rendering
to Excel ?
"jhun_garma@.hotmail.com" wrote:
> guys, I am exporting a report in excel that generates in different
> sheets.
> It seems that only 3 tab or sheets can be generated.
> If the report exceeds 3 sheets all the rest of the sheets combined in
> the last sheet.
> Can anyone confirm this? Does it have any work around or I am just
> doing something wrong?
>
> thanks in advance
>

Exporting to Excel

Hi guys,
I have set up a report that exports perfectly into PDF,
but not into Excel.
The report contains a table with:
- a standard table header
- a table row: with alternating logos & a textbox directly
next to it
- a details area
- a table footer
(By alternating logos, I am referring to having different
images placed into a rectangle and different logos will
appear depending on certain conditions).
The problem: When exported into Excel, the logo/image does
not appear on the same row as the textbox that is directly
next to it. And the rest of the details from the table
gets pushed a few rows down.
Any info would be appreciated.
Regards,
LisaNo takers from Microsoft on this one?
Regards,
Lisa
"Lisa Liputra" wrote:
> Hi guys,
> I have set up a report that exports perfectly into PDF,
> but not into Excel.
> The report contains a table with:
> - a standard table header
> - a table row: with alternating logos & a textbox directly
> next to it
> - a details area
> - a table footer
> (By alternating logos, I am referring to having different
> images placed into a rectangle and different logos will
> appear depending on certain conditions).
> The problem: When exported into Excel, the logo/image does
> not appear on the same row as the textbox that is directly
> next to it. And the rest of the details from the table
> gets pushed a few rows down.
> Any info would be appreciated.
> Regards,
> Lisa
>
>
>

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

exporting SQL data as Access readable file

hi guys.
i'm looking to try and get a web tool together that will basically allow a user to download a file that can be plopped into MS Access, using the data i have on my servers stored in MS SQL Server...
any ideas?nobody, huh?|||bcp out a data file?

You need to be clearer on your process and what you're trying to do..

I'd venture to say that a stored procedure will be called, then using xp_cmdshell bcp out a view or use queryout...

But a little more explination would help us...