When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,
Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Thursday, March 29, 2012
Get Available free space for data file
When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>
Get Available free space for data file
When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:
> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:
> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
Get all records from 1 table
I have a view with the following FROM clause. I want to be able to get ALL
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:
> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:
> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:
> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:
> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql
Tuesday, March 27, 2012
Get a list of files a SQL Server instance can access
I'd like to be able to present my user with a tree view of the devices and
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
GrahamAt the TSQL level, EM uses some extended stored procedures. Run Profiler and
see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It
would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The
closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and
directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQ
L Server database on a
> remote machine to do a backup, when I come to select a filename the browse
button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL
-DMO?
> Thanks in advance for any help
> --
> Graham
>|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:'
David Gugick
Imceda Software
www.imceda.com|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:'
> --
> David Gugick
> Imceda Software
> www.imceda.com
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
GrahamAt the TSQL level, EM uses some extended stored procedures. Run Profiler and
see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It
would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The
closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and
directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQ
L Server database on a
> remote machine to do a backup, when I come to select a filename the browse
button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL
-DMO?
> Thanks in advance for any help
> --
> Graham
>|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:'
David Gugick
Imceda Software
www.imceda.com|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:'
> --
> David Gugick
> Imceda Software
> www.imceda.com
Get a list of files a SQL Server instance can access
I'd like to be able to present my user with a tree view of the devices and
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
Graham
At the TSQL level, EM uses some extended stored procedures. Run Profiler and see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQL Server database on a
> remote machine to do a backup, when I come to select a filename the browse button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL-DMO?
> Thanks in advance for any help
> --
> Graham
>
|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:\'
David Gugick
Imceda Software
www.imceda.com
|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:\'
> --
> David Gugick
> Imceda Software
> www.imceda.com
sql
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
Graham
At the TSQL level, EM uses some extended stored procedures. Run Profiler and see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQL Server database on a
> remote machine to do a backup, when I come to select a filename the browse button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL-DMO?
> Thanks in advance for any help
> --
> Graham
>
|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:\'
David Gugick
Imceda Software
www.imceda.com
|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:\'
> --
> David Gugick
> Imceda Software
> www.imceda.com
sql
Get 1st Value in Group
I have a grouping view and I want to be able to get the 1st value in a
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:
>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:
>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>
Wednesday, March 21, 2012
Generating report is very TEDIOUS
Hello everybody, I have a problem in generating the report. It takes 1 and half hours just to view my report. Is there anyone know how to make it fast. Im using MySQL and i used 16 views for my report thats why its very tedious.
Thank a lot!!!Whatever query or stored procedure u r using execute it outside report and test the time.|||Also check that the fields you are joining on are indexed. When you look at the SQL for the report is there a where clause? If not the entire report is being processed on the local machine. Do you have the option "perform grouping on server" checked?
Thank a lot!!!Whatever query or stored procedure u r using execute it outside report and test the time.|||Also check that the fields you are joining on are indexed. When you look at the SQL for the report is there a where clause? If not the entire report is being processed on the local machine. Do you have the option "perform grouping on server" checked?
Monday, March 12, 2012
Generated RDL Causes Elements to Overlap
Howdy,
I'm generating a series of RDL reports using Python and Cheetah. It
seems to work great with one, huge problem: when I go to view the
reports in Visual Studio or when they're deployed, the report elements
overlap or are out of order.
Is there any non-absolute way to position elements using RDL? I can't
figure out how to even take just 3 textboxes and get them to flow down
a page without specifying their TOP coordinates. Sounds basic, but I'm
butting my head against the wall here. At best, they seem to flow in
reverse order.
Here's an example of some simple RDL that ends up overlapping (in
Visual Studio, which also warns me about overlapping) - any help at
all, conceptually or otherwise, would be much appreciated:
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test box 1</Value>
</Textbox>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 2</Value>
</Textbox>
<Textbox Name="textbox3">
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 3</Value>
</Textbox>
</ReportItems>My suggestion is to post this in the web forum. The newsgroup is pretty much
devoid of MS people and this is a pretty specific and unusual question, very
few people have generated RDL themselves. If you go to the web forum not
only might a MS person be hanging out there and provide an answer, the other
RS MVP hangs out there and he might be able to help you.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<moviegoer22@.gmail.com> wrote in message
news:1185472600.783495.246480@.22g2000hsm.googlegroups.com...
> Howdy,
> I'm generating a series of RDL reports using Python and Cheetah. It
> seems to work great with one, huge problem: when I go to view the
> reports in Visual Studio or when they're deployed, the report elements
> overlap or are out of order.
> Is there any non-absolute way to position elements using RDL? I can't
> figure out how to even take just 3 textboxes and get them to flow down
> a page without specifying their TOP coordinates. Sounds basic, but I'm
> butting my head against the wall here. At best, they seem to flow in
> reverse order.
> Here's an example of some simple RDL that ends up overlapping (in
> Visual Studio, which also warns me about overlapping) - any help at
> all, conceptually or otherwise, would be much appreciated:
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test box 1</Value>
> </Textbox>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 2</Value>
> </Textbox>
> <Textbox Name="textbox3">
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 3</Value>
> </Textbox>
> </ReportItems>
>
I'm generating a series of RDL reports using Python and Cheetah. It
seems to work great with one, huge problem: when I go to view the
reports in Visual Studio or when they're deployed, the report elements
overlap or are out of order.
Is there any non-absolute way to position elements using RDL? I can't
figure out how to even take just 3 textboxes and get them to flow down
a page without specifying their TOP coordinates. Sounds basic, but I'm
butting my head against the wall here. At best, they seem to flow in
reverse order.
Here's an example of some simple RDL that ends up overlapping (in
Visual Studio, which also warns me about overlapping) - any help at
all, conceptually or otherwise, would be much appreciated:
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test box 1</Value>
</Textbox>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 2</Value>
</Textbox>
<Textbox Name="textbox3">
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 3</Value>
</Textbox>
</ReportItems>My suggestion is to post this in the web forum. The newsgroup is pretty much
devoid of MS people and this is a pretty specific and unusual question, very
few people have generated RDL themselves. If you go to the web forum not
only might a MS person be hanging out there and provide an answer, the other
RS MVP hangs out there and he might be able to help you.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<moviegoer22@.gmail.com> wrote in message
news:1185472600.783495.246480@.22g2000hsm.googlegroups.com...
> Howdy,
> I'm generating a series of RDL reports using Python and Cheetah. It
> seems to work great with one, huge problem: when I go to view the
> reports in Visual Studio or when they're deployed, the report elements
> overlap or are out of order.
> Is there any non-absolute way to position elements using RDL? I can't
> figure out how to even take just 3 textboxes and get them to flow down
> a page without specifying their TOP coordinates. Sounds basic, but I'm
> butting my head against the wall here. At best, they seem to flow in
> reverse order.
> Here's an example of some simple RDL that ends up overlapping (in
> Visual Studio, which also warns me about overlapping) - any help at
> all, conceptually or otherwise, would be much appreciated:
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test box 1</Value>
> </Textbox>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 2</Value>
> </Textbox>
> <Textbox Name="textbox3">
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 3</Value>
> </Textbox>
> </ReportItems>
>
Generate view and stored proc scripts retaining depandancy order
Hi,
We need to generate view and stored proc scripts so that they are
generated in the right order. eg
ViewA uses ViewB
Generate
--
ViewB
ViewA
etc
When using Enterprise Manager it just dumps the views and stored procs
alphabetically.
regards
PaulUsing EM, there is no guarantee that it'll generate the SP scripts in the
correct dependency order. To be sure, you need to sort then generated
scripts yourself.
I do this by using a script to scanning the SP scripts, find all the
immediate dependencies, and sort the SP by depdendency.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Paul Sjoerdsma" <paul.sjoerdsma@.redsnapper.nl> wrote in message
news:MPG.19e266ada9d972919896c3@.127.0.0.1...
> Hi,
> We need to generate view and stored proc scripts so that they are
> generated in the right order. eg
> ViewA uses ViewB
> Generate
> --
> ViewB
> ViewA
> etc
> When using Enterprise Manager it just dumps the views and stored procs
> alphabetically.
> regards
> Paul
We need to generate view and stored proc scripts so that they are
generated in the right order. eg
ViewA uses ViewB
Generate
--
ViewB
ViewA
etc
When using Enterprise Manager it just dumps the views and stored procs
alphabetically.
regards
PaulUsing EM, there is no guarantee that it'll generate the SP scripts in the
correct dependency order. To be sure, you need to sort then generated
scripts yourself.
I do this by using a script to scanning the SP scripts, find all the
immediate dependencies, and sort the SP by depdendency.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Paul Sjoerdsma" <paul.sjoerdsma@.redsnapper.nl> wrote in message
news:MPG.19e266ada9d972919896c3@.127.0.0.1...
> Hi,
> We need to generate view and stored proc scripts so that they are
> generated in the right order. eg
> ViewA uses ViewB
> Generate
> --
> ViewB
> ViewA
> etc
> When using Enterprise Manager it just dumps the views and stored procs
> alphabetically.
> regards
> Paul
Friday, March 9, 2012
Generate sysdepends list
:shocked: I can't seem to find a way of gettng a list of the view/stored procedure/UDF dependencies by object. Any ideas?What program are you using to do this?|||I am using ASP and vbscript to access the list and it is not retreiving all of the dependencies.:eek:|||Microsoft SQL doesn't guarantee 100% coverage for sysdepends. There will be some cases where you won't have a row that should be there.
-PatP|||Thanks Pat. I found shrinking the database brings newer dependencies into the list. Odd.:shocked:|||If a DDL script creates an object that references an object that does not yet exist (for instance, it is created later on...), then the first object will be created anyway but an entry will not be added to sysdepends. It should generate a warning to notify you of this.|||I create any dependencies a new store procedure, UDF or view needs first before creating the new piece to avoid the errors associated with referenced unknowns. Thanks for the tip.:)
-PatP|||Thanks Pat. I found shrinking the database brings newer dependencies into the list. Odd.:shocked:|||If a DDL script creates an object that references an object that does not yet exist (for instance, it is created later on...), then the first object will be created anyway but an entry will not be added to sysdepends. It should generate a warning to notify you of this.|||I create any dependencies a new store procedure, UDF or view needs first before creating the new piece to avoid the errors associated with referenced unknowns. Thanks for the tip.:)
Sunday, February 26, 2012
Generate Percentage
I have a report that shows number of units in use for a given day. You can
then drill down and view the number of units in use for a given hour. I want
to display what percentage of units are in use for the given hour. Ex:
Yesterday there were 4 units in use. If you drill down you see that 1 unit
was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
in use.
The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
is 4, that is displayed in the group header. How can I do this?
TIA
PaulPaul,
To achive this u need to pass this value as a parameter to drill down
report and use this value for your calculation in next report. As far as I
know there is now way for you to access field of other report.
Hope this helps
Thanks,
Mahesh
"Paul Timmerman" wrote:
> I have a report that shows number of units in use for a given day. You can
> then drill down and view the number of units in use for a given hour. I want
> to display what percentage of units are in use for the given hour. Ex:
> Yesterday there were 4 units in use. If you drill down you see that 1 unit
> was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
> in use.
> The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
> is 4, that is displayed in the group header. How can I do this?
> TIA
> Paul
>
then drill down and view the number of units in use for a given hour. I want
to display what percentage of units are in use for the given hour. Ex:
Yesterday there were 4 units in use. If you drill down you see that 1 unit
was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
in use.
The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
is 4, that is displayed in the group header. How can I do this?
TIA
PaulPaul,
To achive this u need to pass this value as a parameter to drill down
report and use this value for your calculation in next report. As far as I
know there is now way for you to access field of other report.
Hope this helps
Thanks,
Mahesh
"Paul Timmerman" wrote:
> I have a report that shows number of units in use for a given day. You can
> then drill down and view the number of units in use for a given hour. I want
> to display what percentage of units are in use for the given hour. Ex:
> Yesterday there were 4 units in use. If you drill down you see that 1 unit
> was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
> in use.
> The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
> is 4, that is displayed in the group header. How can I do this?
> TIA
> Paul
>
Subscribe to:
Posts (Atom)