Hi,
Could the following query be simplified/optimized? The database has a table
that maps users to locations, locations to chapters, and sections to
chapters. I need to issue a query that would return a list of sections that
can be assigned to a user (excluding the ones that have already been
assigned) .
@.UserId int
[...]
SELECT SectionID, [Name],
FROM Section
WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID = @.UserID) AND
SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
WHERE ChapterID IN
(SELECT DISTINCT ChapterID FROM ChapterLocation
WHERE LocationID IN
(SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
)
);
I realize that this might not be as clear as it should be but I'd appreciate
_any_ suggestions.
Thanks,
Dan> Could the following query be simplified/optimized?
I think the query below is equivalent. I think indexes on the columns in
the WHERE/JOIN clauses may help performance(e.g. a composite index on
UserSection UserID, SectionID).
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
) AND
EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Could the following query be simplified/optimized? The database has a
> table that maps users to locations, locations to chapters, and sections to
> chapters. I need to issue a query that would return a list of sections
> that can be assigned to a user (excluding the ones that have already been
> assigned) .
> @.UserId int
> [...]
> SELECT SectionID, [Name],
> FROM Section
> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID => @.UserID) AND
> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
> WHERE ChapterID IN
> (SELECT DISTINCT ChapterID FROM ChapterLocation
> WHERE LocationID IN
> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
> )
> );
> I realize that this might not be as clear as it should be but I'd
> appreciate _any_ suggestions.
> Thanks,
> Dan
>|||Thanks for the reply. The query looks clean but there seems to be a problem
with EXISTS() in the WHERE clause. Some of the sections included in the
result set are not assigned to the user's location(s) (implicitly through
chapters). I.e. the query selects chapters that are not assigned to user's
locations in addition to the chapters that are assigned.
Any suggestions?
Thanks,
Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
> I think the query below is equivalent. I think indexes on the columns in
> the WHERE/JOIN clauses may help performance(e.g. a composite index on
> UserSection UserID, SectionID).
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> ) AND
> EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of sections
>> that can be assigned to a user (excluding the ones that have already been
>> assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||> Thanks for the reply. The query looks clean but there seems to be a
> problem
> with EXISTS() in the WHERE clause. Some of the sections included in the
> result set are not assigned to the user's location(s) (implicitly through
> chapters). I.e. the query selects chapters that are not assigned to
> user's locations in addition to the chapters that are assigned.
> Any suggestions?
I missed the join from ChapterSection.SectionID back to Section.SectionID.
See the corrected query below. If this still doesn't work for you, please
post the table DDL and sample data so that I can test the solution.
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
)
AND EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID
AND cs.SectionID = s.SectionID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
> Thanks for the reply. The query looks clean but there seems to be a
> problem with EXISTS() in the WHERE clause. Some of the sections included
> in the result set are not assigned to the user's location(s) (implicitly
> through chapters). I.e. the query selects chapters that are not assigned
> to user's locations in addition to the chapters that are assigned.
> Any suggestions?
> Thanks,
> Dan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns in
>> the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||Thanks. It works now.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5F09A613-CEF3-45CA-B2EA-6A3D39CB69D5@.microsoft.com...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem
>> with EXISTS() in the WHERE clause. Some of the sections included in the
>> result set are not assigned to the user's location(s) (implicitly through
>> chapters). I.e. the query selects chapters that are not assigned to
>> user's locations in addition to the chapters that are assigned.
>> Any suggestions?
> I missed the join from ChapterSection.SectionID back to Section.SectionID.
> See the corrected query below. If this still doesn't work for you, please
> post the table DDL and sample data so that I can test the solution.
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> )
> AND EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID
> AND cs.SectionID = s.SectionID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem with EXISTS() in the WHERE clause. Some of the sections included
>> in the result set are not assigned to the user's location(s) (implicitly
>> through chapters). I.e. the query selects chapters that are not assigned
>> to user's locations in addition to the chapters that are assigned.
>> Any suggestions?
>> Thanks,
>> Dan
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns
>> in the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID
>> = @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID =>> @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>>
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to show the border of the table?
hi,
How to adjust/display/hide the table border(including all cells)?
I see in the property page, there is a BorderStyle for the table, but it only changes the outline of the table border style, I want to change the border style including all cells, like we did in html page, is there an easy way to do this, or I have to make the setting for all cells?
Thanks
I don't think there is a way to force gridlines for the table. You'll probably need to specify the borders of the cells.|||
Oh...
When I created a report table by the wizard, it can be set to have the gridlines. From the rdl file, seems it sets this for each cell, not the whole table.
How to show table header, footer even when the dataset is empty?
hi,
Just got a question about the report. When my report does not get any data from database, like dataset is empty, it will show nothing. This is reasonable. But what our customers want is, they still need to show the table header, footer, but no data.
Thanks.
How to show table header, footer even when the dataset is empty?
hi,
Just got a question about the report. When my report does not get any
data from database, like dataset is empty, it will show nothing. This
is reasonable. But what our customers want is, they still need to show
the table header, footer, but no data.
ThanksHi Nick,
What is in the table headers and footers...dataset field values only
or possibly the header or footer has text? If you do have text in the
header or footer and they don't appear, check the NoRows property for
the table...any text or string value expression you set will cause the
table to not appear in favor of the expression you set. Text in
headers or footers does display, by default, even if a table has no
records...so you can also check for condittional logic that is hiding
the header or footer.
Maybe you can explain what your table headers and footers have in them?
If it's all dataset field vlaues-what shoud appear?
MattA
Reporting Services Newsletter at www.reportarchitex.com
Just got a question about the report. When my report does not get any
data from database, like dataset is empty, it will show nothing. This
is reasonable. But what our customers want is, they still need to show
the table header, footer, but no data.
ThanksHi Nick,
What is in the table headers and footers...dataset field values only
or possibly the header or footer has text? If you do have text in the
header or footer and they don't appear, check the NoRows property for
the table...any text or string value expression you set will cause the
table to not appear in favor of the expression you set. Text in
headers or footers does display, by default, even if a table has no
records...so you can also check for condittional logic that is hiding
the header or footer.
Maybe you can explain what your table headers and footers have in them?
If it's all dataset field vlaues-what shoud appear?
MattA
Reporting Services Newsletter at www.reportarchitex.com
How to show table header in each page
I want the table header appears in each page, how can i do this?
Thanks.
JasonClick on the Table header row handler,go to properties, you can see
Repeatonnewpage property,Enable it.that's it
Jason Chan wrote:
> I want the table header appears in each page, how can i do this?
> Thanks.
> Jason|||Got it. Thanks
"RajDeep" wrote:
> Click on the Table header row handler,go to properties, you can see
> Repeatonnewpage property,Enable it.that's it
> Jason Chan wrote:
> > I want the table header appears in each page, how can i do this?
> >
> > Thanks.
> >
> > Jason
>sql
Thanks.
JasonClick on the Table header row handler,go to properties, you can see
Repeatonnewpage property,Enable it.that's it
Jason Chan wrote:
> I want the table header appears in each page, how can i do this?
> Thanks.
> Jason|||Got it. Thanks
"RajDeep" wrote:
> Click on the Table header row handler,go to properties, you can see
> Repeatonnewpage property,Enable it.that's it
> Jason Chan wrote:
> > I want the table header appears in each page, how can i do this?
> >
> > Thanks.
> >
> > Jason
>sql
How to show rows of dataset as columns in report
Hi,
I have a dataset whose rows need to shown as columns of a table in report.
Is it possible to do so?
Please help
regards,
SachinCan you use a matrix instead of a table?
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> Hi,
> I have a dataset whose rows need to shown as columns of a table in report.
> Is it possible to do so?
> Please help
> regards,
> Sachin|||Hi,
I am quite new to reporting services.
Wll matrix solve my problem?
Please help.
regards,
Sachin.
"Steve MunLeeuw" wrote:
> Can you use a matrix instead of a table?
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> > Hi,
> >
> > I have a dataset whose rows need to shown as columns of a table in report.
> > Is it possible to do so?
> >
> > Please help
> > regards,
> > Sachin
>
>|||I think that it will. The Adventure Works Sample Reports that ships with
SSRS 2005 has a Company Sales report that demonstrates the use of the
matrix. It has Order Year and Order Qtr as columns across the top.
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:334F642D-F816-46FD-BBF4-7F8D1E0B8276@.microsoft.com...
> Hi,
> I am quite new to reporting services.
> Wll matrix solve my problem?
> Please help.
> regards,
> Sachin.
>
> "Steve MunLeeuw" wrote:
>> Can you use a matrix instead of a table?
>> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
>> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
>> > Hi,
>> >
>> > I have a dataset whose rows need to shown as columns of a table in
>> > report.
>> > Is it possible to do so?
>> >
>> > Please help
>> > regards,
>> > Sachin
>>sql
I have a dataset whose rows need to shown as columns of a table in report.
Is it possible to do so?
Please help
regards,
SachinCan you use a matrix instead of a table?
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> Hi,
> I have a dataset whose rows need to shown as columns of a table in report.
> Is it possible to do so?
> Please help
> regards,
> Sachin|||Hi,
I am quite new to reporting services.
Wll matrix solve my problem?
Please help.
regards,
Sachin.
"Steve MunLeeuw" wrote:
> Can you use a matrix instead of a table?
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> > Hi,
> >
> > I have a dataset whose rows need to shown as columns of a table in report.
> > Is it possible to do so?
> >
> > Please help
> > regards,
> > Sachin
>
>|||I think that it will. The Adventure Works Sample Reports that ships with
SSRS 2005 has a Company Sales report that demonstrates the use of the
matrix. It has Order Year and Order Qtr as columns across the top.
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:334F642D-F816-46FD-BBF4-7F8D1E0B8276@.microsoft.com...
> Hi,
> I am quite new to reporting services.
> Wll matrix solve my problem?
> Please help.
> regards,
> Sachin.
>
> "Steve MunLeeuw" wrote:
>> Can you use a matrix instead of a table?
>> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
>> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
>> > Hi,
>> >
>> > I have a dataset whose rows need to shown as columns of a table in
>> > report.
>> > Is it possible to do so?
>> >
>> > Please help
>> > regards,
>> > Sachin
>>sql
How to show roles with permissions to objects
Hello,
>
>
Thank you. I don't know how I overlooked that one. Sometimes I wish
BOL was laid out a little differently.
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
BruceHave you checked "sp_helprotect" , this one is permissions for all objects,
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Bruce" <deluxeinformation@.gmail.comwrote in message
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...
Quote:
Originally Posted by
Hello,
>
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
>
Bruce
>
Quote:
Originally Posted by
Have you checked "sp_helprotect" , this one is permissions for all objects,
>
--
>
Jack Vamvas
___________________________________
The latest IT jobs -www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
"Bruce" <deluxeinformat...@.gmail.comwrote in message
>
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...
>
Quote:
Originally Posted by
Hello,
>
Quote:
Originally Posted by
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
>
Quote:
Originally Posted by
Bruce
Thank you. I don't know how I overlooked that one. Sometimes I wish
BOL was laid out a little differently.
Bruce
how to show records with JOIN?
Hi ,
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
Examples:
First Tbl
ProductID Name TradePrice
1 Jumper ￡1.00
2 Jeans ￡3.00
3 Shoes ￡5.00
4 Hat ￡2.00
Second Tbl
ProductID CustomerID IndivPrice
1 teste ￡0.50
2 othercustomer ￡2.50
3 teste ￡4.50
What I want in the results is:
ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)
1 Jumper ￡1.00 ￡0.50 teste
2 Jeans ￡3.00
3 Shoes ￡5.00 ￡4.50 teste
4 Hat ￡2.00
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
HELP!!!!!
Give a look to the SELECT article in books online. Maybe something like:
|||declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbldeclare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl
select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000 2.50 othercustomer
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/
Hi Kent,
Thanks for the reply..
Your select statement looks virtually the same as mine!
However your's is missing the WHERE clause - and this is what is causing the problem.
My WHERE clause needs to bring back records that either have no CustomerID, or the specific CustomeID I ask for in the WHERE statement.
However the WHERE clause I have inserted (see my first post) simply does not do it. If I put in a Customer ID into my where clause that doesn't have an indivprice records.. then it simply returns nothing...
whereas it should return all of the records... but obivously these would not have an indivprice for them
|||Is this better:
declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbldeclare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl
select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId
and customerId = 'teste'/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/
I have a question: Are you wanting results 2 and 4 removed?
|||
thanks!! - that's perfect!!
Can you let me know what is different about your query.. I see that indivprice has a convert on it and there is a Coalesce statement..
What are these for?
|||Hang on and I'll give it a go.
The COALESCE / VARCHAR business is to replace an output that would otherwise say NULL with blank space.
After I reworked your original query and used your table names what I got was:
|||SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
coalesce(convert(varchar(21), IndivPrice), '') as IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 0.50 teste
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/
- excellent - thank you very much...
|||one final question.
If I use your query - then the Indiv price is now converted into a varchar and no longer is a money field.
I need this to be a money field for my code (it's used in a shopping basket...
How would I do this..
(I've tried converting it in my Server Side ASP using CLng, but it won't do it..
|||Then don't use the VARCHAR / COALESCE on this particular field -- provided that your application can handle the situation when the field is a NULL output. I just realized that when I reworked your query I dropped a row; is that wanted?|||just noticed that myself... no I need all results back..
why did it drop a record?
|||To avoid dropping that record one of the lineds needs to be moved up and become part of the JOIN instead of part of the WHERE clause. Also, I removed the conversion of the PRICE field:
SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')
/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 .5000 teste
2 Jeans 3.0000 NULL
3 Shoes 5.0000 4.5000 teste
4 Hat 2.0000 NULL
*/
how to show progress percent
I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article. when i use osql I just want whenever I execute a stat
ement the osql or the script could return a number!
hwo can i use Transact-SQL to do this?
Thanks a lot
Out of the box for standard initialization this is not possible. If you want
to monitor the standard process you could poll the relevant history table -
MSmerge_history for merge and MSdistribution_history for transactional and
snapshot and filter these results. If you are implementing your own script
to create the tables at the subscriber you could maintain your own counter
and increment it each time a table is added.
HTH,
Paul Ibison
|||the replication ActiveX controls have this functionality. Unfortunately you have to use the status event which is not accessible from a script.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:
> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||I could maintain my own counter and increment it but how can i return the counter to the outside when executed each one statement ?
In fact, now I want to build a setup program with InstallShield Profession and during the install, a replication should be done. The database which need to be replicated is very big. So I want to show a dialog which show the progress precent to improve th
e setup UI. It is not complex to show a dialog in InstallShield script. but I can not return the counter in the MSSQL script to the InstallShield script, when using the osql utility( like osql ... -i script file name ).
Could you like to give any suggestion?
Thanks a lot
"Paul Ibison" wrote:
> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||Lowiq,
you have a few choices. You can have many scripts and get a count by virtue
of the number of scripts processed. If InstallShield is multi-threaded
(don't know offhand), you can set off the osql script asynchronously. The
script would increment a counter in a table each time it adds a table,
populates a table etc. Your main execution thread would poll this counter
table to see the level of progress. Alternatively you could poll the
relevant history table although this would require a bit of complex
filtering. BTW nosync initializations can be a little restrictive as far as
future modifications are concerned.
HTH,
Paul Ibison
"lowiq" <lowiq@.discussions.microsoft.com> wrote in message
news:331E585D-28A2-473A-8B6D-EA38D2A841BA@.microsoft.com...
> I could maintain my own counter and increment it but how can i return the
counter to the outside when executed each one statement ?
> In fact, now I want to build a setup program with InstallShield Profession
and during the install, a replication should be done. The database which
need to be replicated is very big. So I want to show a dialog which show the
progress precent to improve the setup UI. It is not complex to show a dialog
in InstallShield script. but I can not return the counter in the MSSQL
script to the InstallShield script, when using the osql utility( like osql
... -i script file name ).[vbcol=seagreen]
> Could you like to give any suggestion?
> Thanks a lot
> "Paul Ibison" wrote:
want[vbcol=seagreen]
table -[vbcol=seagreen]
and[vbcol=seagreen]
script[vbcol=seagreen]
counter[vbcol=seagreen]
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article. when i use osql I just want whenever I execute a stat
ement the osql or the script could return a number!
hwo can i use Transact-SQL to do this?
Thanks a lot
Out of the box for standard initialization this is not possible. If you want
to monitor the standard process you could poll the relevant history table -
MSmerge_history for merge and MSdistribution_history for transactional and
snapshot and filter these results. If you are implementing your own script
to create the tables at the subscriber you could maintain your own counter
and increment it each time a table is added.
HTH,
Paul Ibison
|||the replication ActiveX controls have this functionality. Unfortunately you have to use the status event which is not accessible from a script.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:
> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||I could maintain my own counter and increment it but how can i return the counter to the outside when executed each one statement ?
In fact, now I want to build a setup program with InstallShield Profession and during the install, a replication should be done. The database which need to be replicated is very big. So I want to show a dialog which show the progress precent to improve th
e setup UI. It is not complex to show a dialog in InstallShield script. but I can not return the counter in the MSSQL script to the InstallShield script, when using the osql utility( like osql ... -i script file name ).
Could you like to give any suggestion?
Thanks a lot
"Paul Ibison" wrote:
> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||Lowiq,
you have a few choices. You can have many scripts and get a count by virtue
of the number of scripts processed. If InstallShield is multi-threaded
(don't know offhand), you can set off the osql script asynchronously. The
script would increment a counter in a table each time it adds a table,
populates a table etc. Your main execution thread would poll this counter
table to see the level of progress. Alternatively you could poll the
relevant history table although this would require a bit of complex
filtering. BTW nosync initializations can be a little restrictive as far as
future modifications are concerned.
HTH,
Paul Ibison
"lowiq" <lowiq@.discussions.microsoft.com> wrote in message
news:331E585D-28A2-473A-8B6D-EA38D2A841BA@.microsoft.com...
> I could maintain my own counter and increment it but how can i return the
counter to the outside when executed each one statement ?
> In fact, now I want to build a setup program with InstallShield Profession
and during the install, a replication should be done. The database which
need to be replicated is very big. So I want to show a dialog which show the
progress precent to improve the setup UI. It is not complex to show a dialog
in InstallShield script. but I can not return the counter in the MSSQL
script to the InstallShield script, when using the osql utility( like osql
... -i script file name ).[vbcol=seagreen]
> Could you like to give any suggestion?
> Thanks a lot
> "Paul Ibison" wrote:
want[vbcol=seagreen]
table -[vbcol=seagreen]
and[vbcol=seagreen]
script[vbcol=seagreen]
counter[vbcol=seagreen]
How to show group header/footer again after hiding them?
I have started to work on a report with table data region, and there
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.
How to show empty rows in crystal report when data reaches to EOF
I have designed a report in Crystal Report .Net with tabular Layout. I want to have table layout for empty rows in last page. But Report Detail Section is repeated only for record count, no more. How can I show blank rows in report?At the Report footer have a table structure with empty rows
Otherwise post some sample data and the format you want
Otherwise post some sample data and the format you want
Monday, March 26, 2012
how to show columns with two decimal places
HI ,
I've table whose output is some coumns. The colums show 6 digits after decimal point . My problem is i want to show only two digits after decimal point .. How to do using query..
Thanks
From your question i seem that you are having the FLOAT datatype. You can do this in two ways
1) Modify the table column to DECIMAL(10,2), if you always want to have results in the 2 deciaml place
or
2) SELECT cast (2.456576 AS decimal(10,2)), if you want temp. solution.
Note: This will Round-off the values.
Gurpreet S. Gill
|||
You can use the following approaches,
With Rounded Value:
select convert(Numeric(7,2),ColumnName) From TableName
here it will round/adjust your number to 2 digit decimal
Without Rounded Value:
Select Convert(Numeric(7,2), Round(ColumnName,2,1)) From TableName
here it will truncate the number upto 2 digit decimal and it only show the 2 digit dicimal on your output
|||Two best practices:
1. The best thing to do is change the table to store the data as the datatype you want to use.
2. If the data needs to be stored like this, but you want to display it this way, the absolute best way of doing this is to use the front end to format the data as you want it. The front end already has to work to display the data in some format, so specifying it as you desire will be best
If you need to do it in T-SQL, then the other replies to use CAST is the next best thing.
|||Already my table datatype is fixed i can't change the datatype. I'm just show output in gridview ...Tell me something which can be done in Grid view or else i've change the whole datatype of table|||As suggested by others, i too says "the best place to do this is the FRONT END". As, when the front end read the data from Database, it internally converts the data into its readable form.ok tell us which Language you are using & its version ?
which Grid ? and other information. like connection type ...etc.
Gurpreet S. Gill|||Hi i'm using asp.net Grid View (vb.net)|||This may help you
http://www.netomatix.com/development/GridViewDataFormatting.aspx
More reading regarding the "DataFormatString"
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
http://devauthority.com/blogs/sskokku/archive/2006/08/17/1962.aspx
Gurpreet S.Gill|||More Reading
this the exactly what you want
http://forums.asp.net/thread/1463906.aspx
http://msconline.maconstate.edu/Tutorials/ASPNET2/ASPNET07/aspnet07-01.aspx
Gurpreet S. Gil
how to show columns with two decimal places
HI ,
I've table whose output is some coumns. The colums show 6 digits after decimal point . My problem is i want to show only two digits after decimal point .. How to do using query..
Thanks
From your question i seem that you are having the FLOAT datatype. You can do this in two ways
1) Modify the table column to DECIMAL(10,2), if you always want to have results in the 2 deciaml place
or
2) SELECT cast (2.456576 AS decimal(10,2)), if you want temp. solution.
Note: This will Round-off the values.
Gurpreet S. Gill
|||
You can use the following approaches,
With Rounded Value:
select convert(Numeric(7,2),ColumnName) From TableName
here it will round/adjust your number to 2 digit decimal
Without Rounded Value:
Select Convert(Numeric(7,2), Round(ColumnName,2,1)) From TableName
here it will truncate the number upto 2 digit decimal and it only show the 2 digit dicimal on your output
|||Two best practices:
1. The best thing to do is change the table to store the data as the datatype you want to use.
2. If the data needs to be stored like this, but you want to display it this way, the absolute best way of doing this is to use the front end to format the data as you want it. The front end already has to work to display the data in some format, so specifying it as you desire will be best
If you need to do it in T-SQL, then the other replies to use CAST is the next best thing.
|||Already my table datatype is fixed i can't change the datatype. I'm just show output in gridview ...Tell me something which can be done in Grid view or else i've change the whole datatype of table|||As suggested by others, i too says "the best place to do this is the FRONT END". As, when the front end read the data from Database, it internally converts the data into its readable form.ok tell us which Language you are using & its version ?
which Grid ? and other information. like connection type ...etc.
Gurpreet S. Gill|||Hi i'm using asp.net Grid View (vb.net)|||This may help you
http://www.netomatix.com/development/GridViewDataFormatting.aspx
More reading regarding the "DataFormatString"
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
http://devauthority.com/blogs/sskokku/archive/2006/08/17/1962.aspx
Gurpreet S.Gill|||More Reading
this the exactly what you want
http://forums.asp.net/thread/1463906.aspx
http://msconline.maconstate.edu/Tutorials/ASPNET2/ASPNET07/aspnet07-01.aspx
Gurpreet S. Gilsql
How to show both "ID and Name" in the NemeColumn of a dimension?
Hi All,
When creating a new dimension we are restricted in selecting one coulmn from the table to be shown as the "NameColumn" of the dimension; i need to display both ID and Name from the table as the "NameColumn", how could i do this?
Since in AS 2000 this can be done easily
Thanks,
Ghadeer Omari
Since you want to show ID and name, you have to somehow concatinate them. And there could be multiple ways to do that - with space, with dash, etc. I usually in such cases go to data source views and create named calculations :
FullName: ID + '-' + Name
Then I use new named calculation in name column.
Vidas Matelis
|||Dear Matelis,
Thanks very much this works.
how to show all the permission for a role
Hello,
I have an application database that has a role with specific permission to
each objects like table, proc, views.
There are about 200+ objects with different permission. some are select,
insert, ddr.
How can I write a SQL statement to show a report of this role with all the
different objects permissions.
I was able to go to role in Enterprise Manager and select permission.
I need an excel report. It would best it I can get the same results that I
see in EM using Query Analyzer.
You can use Northwind.
Any suggestions.Hi,
Execute the system stored procedure
sp_helprotect null,<Role Name>
Thanks
Hari
SQL Server MVP
"SQL Apprentice" wrote:
> Hello,
> I have an application database that has a role with specific permission to
> each objects like table, proc, views.
> There are about 200+ objects with different permission. some are select,
> insert, ddr.
> How can I write a SQL statement to show a report of this role with all the
> different objects permissions.
> I was able to go to role in Enterprise Manager and select permission.
> I need an excel report. It would best it I can get the same results that
I
> see in EM using Query Analyzer.
> You can use Northwind.
> Any suggestions.
>
>|||Thank you Hari.
"Hari Pra
" <HariPra@.discussions.microsoft.com> wrote in message
news:FEB28171-446D-4EEB-8708-8BEFA4D3B0DA@.microsoft.com...
> Hi,
> Execute the system stored procedure
> sp_helprotect null,<Role Name>
> Thanks
> Hari
> SQL Server MVP
>
> "SQL Apprentice" wrote:
>
to
the
that I
I have an application database that has a role with specific permission to
each objects like table, proc, views.
There are about 200+ objects with different permission. some are select,
insert, ddr.
How can I write a SQL statement to show a report of this role with all the
different objects permissions.
I was able to go to role in Enterprise Manager and select permission.
I need an excel report. It would best it I can get the same results that I
see in EM using Query Analyzer.
You can use Northwind.
Any suggestions.Hi,
Execute the system stored procedure
sp_helprotect null,<Role Name>
Thanks
Hari
SQL Server MVP
"SQL Apprentice" wrote:
> Hello,
> I have an application database that has a role with specific permission to
> each objects like table, proc, views.
> There are about 200+ objects with different permission. some are select,
> insert, ddr.
> How can I write a SQL statement to show a report of this role with all the
> different objects permissions.
> I was able to go to role in Enterprise Manager and select permission.
> I need an excel report. It would best it I can get the same results that
I
> see in EM using Query Analyzer.
> You can use Northwind.
> Any suggestions.
>
>|||Thank you Hari.
"Hari Pra
" <HariPra@.discussions.microsoft.com> wrote in messagenews:FEB28171-446D-4EEB-8708-8BEFA4D3B0DA@.microsoft.com...
> Hi,
> Execute the system stored procedure
> sp_helprotect null,<Role Name>
> Thanks
> Hari
> SQL Server MVP
>
> "SQL Apprentice" wrote:
>
to
the
that I
How to show all tables info in Task Pad?
In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.
1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?
2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?
Thanks.Task pad should show Next and Last options in the bottom of the page.
You can also get all user table info by executing this sql..
select * from information_schema.tables where table_type like 'BASE TABLE'|||First, in TaskPad, there is no next or last button, second that line of code you gave:
select * from information_schema.tables where table_type like 'BASE TABLE'
Did not return the # of rows and KB size of all of my tables.
THis is what I am looking for.
Anyone else know?
I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.|||I'd suggest:SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS data_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid = 255 THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
, Object_Name(si.id)
FROM dbo.sysindexes AS si
GROUP BY si.id-PatP|||Pat,
What does that code do. Here was my output:
(20 row(s) affected)
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.|||sp_spaceused?
EDIT: Found this...
USE Northwind
GO
SET NOCOUNT ON
GO
CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO
DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'
WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename
SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END
SET NOCOUNT OFF
GO
SELECT * FROM #SpaceUsed
GO
DROP TABLE #SpaceUSed
GO|||Pat,
What does that code do. Here was my output:
(20 row(s) affected)
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.Change the 5s to 15s and try again.
It shows some interesting space observations, by table.
-PatP|||pat,
That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.
Is this what is expected? Is the data_mb column the table size?
Thanks for the help, it is greatly appreciated.|||Brett,
Wonderful!!!!!!!!!!
That was it!!!!!!
Thanks a million!!!!!!|||Does my total match the taskpad total?
-PatP
1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?
2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?
Thanks.Task pad should show Next and Last options in the bottom of the page.
You can also get all user table info by executing this sql..
select * from information_schema.tables where table_type like 'BASE TABLE'|||First, in TaskPad, there is no next or last button, second that line of code you gave:
select * from information_schema.tables where table_type like 'BASE TABLE'
Did not return the # of rows and KB size of all of my tables.
THis is what I am looking for.
Anyone else know?
I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.|||I'd suggest:SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS data_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid = 255 THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
, Object_Name(si.id)
FROM dbo.sysindexes AS si
GROUP BY si.id-PatP|||Pat,
What does that code do. Here was my output:
(20 row(s) affected)
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.|||sp_spaceused?
EDIT: Found this...
USE Northwind
GO
SET NOCOUNT ON
GO
CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO
DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'
WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename
SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END
SET NOCOUNT OFF
GO
SELECT * FROM #SpaceUsed
GO
DROP TABLE #SpaceUSed
GO|||Pat,
What does that code do. Here was my output:
(20 row(s) affected)
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.Change the 5s to 15s and try again.
It shows some interesting space observations, by table.
-PatP|||pat,
That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.
Is this what is expected? Is the data_mb column the table size?
Thanks for the help, it is greatly appreciated.|||Brett,
Wonderful!!!!!!!!!!
That was it!!!!!!
Thanks a million!!!!!!|||Does my total match the taskpad total?
-PatP
How to show all field names and data in two columns i.e. Pivot / c
What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'
Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/...pts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'
sql
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'
Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/...pts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'
sql
How to show all field names and data in two columns i.e. Pivot / c
What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical...ipts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical...ipts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'
How to show all field names and data in two columns i.e. Pivot / c
What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id = '123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/SQL6x70Scripts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id => '123'
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id = '123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/SQL6x70Scripts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id => '123'
How to show all aliases in SQL 7 ?
Does anybody know a command that shows ALL aliases on the database?
Or could I search for them in the system table and make a SELECT for them?
pls. helpRE: Q1 Does anybody know a command that shows ALL aliases on the database?
Or could I search for them in the system table and make a SELECT for them?
pls. help
A1 To get the alias [member] or user status for each login on a Sql Server run:
exec sp_helplogins
If run without specifing a login, the information will appear in the last column (UserOrAlias) of the second result set that the sp_helplogins stored proc returns.
Or could I search for them in the system table and make a SELECT for them?
pls. helpRE: Q1 Does anybody know a command that shows ALL aliases on the database?
Or could I search for them in the system table and make a SELECT for them?
pls. help
A1 To get the alias [member] or user status for each login on a Sql Server run:
exec sp_helplogins
If run without specifing a login, the information will appear in the last column (UserOrAlias) of the second result set that the sp_helplogins stored proc returns.
Subscribe to:
Posts (Atom)