I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Parallel execution of DTS steps.
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
MaxYes, you can. Basically, tasks run in parallel, unless you have set up
workflows that force them to be run serially. That said, if your source is
the same for all of your data pumps, then the process gets serialized, since
the connection is in use. In that case, create multiple connections, each
of which point to the same server.
For example, if you are pumping from ServerA to ServerB, create, say, 4
ServerA connections - ServrA1, ServerA2, etc. and the same goes for ServerB.
Then, make sure your pumps use the different connections.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eobZWSBWGHA.5044@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
Max|||Then you will have to use a different connection for each datapump.
AMB
"Maxwell2006" wrote:
> Hi,
>
> I have a DTS package that exports 50 database tables from SQL Server to
> Oracle.
> At this point the DTS package runs steps after each other (serial
> execution). Since both servers are extremely powerful, the can handle more
> than one concurrent DTS step at a time.
>
> Is there any way to have DTS execute five or six steps in parallel fashion?
>
> Any help would be appreciated,
> Max
>
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
MaxYes, you can. Basically, tasks run in parallel, unless you have set up
workflows that force them to be run serially. That said, if your source is
the same for all of your data pumps, then the process gets serialized, since
the connection is in use. In that case, create multiple connections, each
of which point to the same server.
For example, if you are pumping from ServerA to ServerB, create, say, 4
ServerA connections - ServrA1, ServerA2, etc. and the same goes for ServerB.
Then, make sure your pumps use the different connections.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eobZWSBWGHA.5044@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
Max|||Then you will have to use a different connection for each datapump.
AMB
"Maxwell2006" wrote:
> Hi,
>
> I have a DTS package that exports 50 database tables from SQL Server to
> Oracle.
> At this point the DTS package runs steps after each other (serial
> execution). Since both servers are extremely powerful, the can handle more
> than one concurrent DTS step at a time.
>
> Is there any way to have DTS execute five or six steps in parallel fashion?
>
> Any help would be appreciated,
> Max
>
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp
Monday, March 12, 2012
Paging in SQL 2005 - Row_Number()
I am trying to use the new Sql 2005 feature Row_Number, and as a base point,
used one of ScottGu's excellent blogs as a template.
(http://weblogs.asp.net/scottgu/arch.../07/434787.aspx). I modified
it for my purpose and as written below, works fine.,
HOWEVER ...
There are a few fields that I would like to capture that are not on either
table (The Stores table or the Products table). The columns are on the Media
table that us linked to the products talbe by the ProductID
(
SELECT dbo.Media.MediaURL, ImageLevel
FROM dbo.Media INNER JOIN
dbo.Products ON dbo.Media.ProductID =
dbo.Products.ProductID
)
So, in the following SPROC, how can I modify it so that I can include these
new column values? I have tried quite a few combinations, but get errors.
--Here is the SPROC that
works --
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetProductsByStoreIDPaged]
@.StoreId INT,
@.PageIndex INT,
@.NumRows INT,
@.StoreName nvarchar(50) OUTPUT,
@.StoreProductCount INT OUTPUT
AS
BEGIN
SELECT @.StoreProductCount=(SELECT COUNT(*) FROM Products where
Products.StoreId=@.StoreId)
SELECT @.StoreName=(Select StoreName FROM Stores Where
Stores.StoreID=@.StoreId)
Declare @.startRowIndex INT;
set @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
With ProductEntries as (
--here the selects start ...
SELECT ROW_NUMBER() OVER
(ORDER BY ProductId ASC) as Row,
ProductId,
StoreId, ProductName, ImageURLDisplay
FROM
Products
WHERE StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
END
---
Here is the original SPROC without paging (that works fine):
SELECT TOP 100 PERCENT dbo.Stores.StoreID, dbo.Stores.StoreName,
dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.Description,
dbo.Media.MediaURL, dbo.Media.ImageLevel
FROM dbo.Products INNER JOIN
dbo.Stores ON dbo.Products.StoreID = dbo.Stores.StoreID INNER JOIN
dbo.Media ON dbo.Products.ProductID = dbo.Media.ProductID
WHERE (dbo.Media.ImageLevel = 1) AND
(dbo.Stores.StoreID = @.StoreID)
ORDER BY dbo.Media.ImageSortCol, dbo.Products.ProductID DESC
----
I am trying to transform the original SPROC above into a paged SPROC a` la
ScottGu.
Thanks to all.
PaulShould be straightforward, replace your CTE statement with
With ProductEntries as (
SELECT ROW_NUMBER() OVER
(ORDER BY p.ProductId ASC) as Row,
p.ProductId,
p.StoreId, p.ProductName, p.ImageURLDisplay,m.MediaURL
FROM
Products p
INNER JOIN Media m ON m.ProductID=p.ProductID
WHERE p.StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay ,MediaURL
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
used one of ScottGu's excellent blogs as a template.
(http://weblogs.asp.net/scottgu/arch.../07/434787.aspx). I modified
it for my purpose and as written below, works fine.,
HOWEVER ...
There are a few fields that I would like to capture that are not on either
table (The Stores table or the Products table). The columns are on the Media
table that us linked to the products talbe by the ProductID
(
SELECT dbo.Media.MediaURL, ImageLevel
FROM dbo.Media INNER JOIN
dbo.Products ON dbo.Media.ProductID =
dbo.Products.ProductID
)
So, in the following SPROC, how can I modify it so that I can include these
new column values? I have tried quite a few combinations, but get errors.
--Here is the SPROC that
works --
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetProductsByStoreIDPaged]
@.StoreId INT,
@.PageIndex INT,
@.NumRows INT,
@.StoreName nvarchar(50) OUTPUT,
@.StoreProductCount INT OUTPUT
AS
BEGIN
SELECT @.StoreProductCount=(SELECT COUNT(*) FROM Products where
Products.StoreId=@.StoreId)
SELECT @.StoreName=(Select StoreName FROM Stores Where
Stores.StoreID=@.StoreId)
Declare @.startRowIndex INT;
set @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
With ProductEntries as (
--here the selects start ...
SELECT ROW_NUMBER() OVER
(ORDER BY ProductId ASC) as Row,
ProductId,
StoreId, ProductName, ImageURLDisplay
FROM
Products
WHERE StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
END
---
Here is the original SPROC without paging (that works fine):
SELECT TOP 100 PERCENT dbo.Stores.StoreID, dbo.Stores.StoreName,
dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.Description,
dbo.Media.MediaURL, dbo.Media.ImageLevel
FROM dbo.Products INNER JOIN
dbo.Stores ON dbo.Products.StoreID = dbo.Stores.StoreID INNER JOIN
dbo.Media ON dbo.Products.ProductID = dbo.Media.ProductID
WHERE (dbo.Media.ImageLevel = 1) AND
(dbo.Stores.StoreID = @.StoreID)
ORDER BY dbo.Media.ImageSortCol, dbo.Products.ProductID DESC
----
I am trying to transform the original SPROC above into a paged SPROC a` la
ScottGu.
Thanks to all.
PaulShould be straightforward, replace your CTE statement with
With ProductEntries as (
SELECT ROW_NUMBER() OVER
(ORDER BY p.ProductId ASC) as Row,
p.ProductId,
p.StoreId, p.ProductName, p.ImageURLDisplay,m.MediaURL
FROM
Products p
INNER JOIN Media m ON m.ProductID=p.ProductID
WHERE p.StoreId=@.StoreId
)
Select ProductId, StoreId, ProductName,
ImageURLDIsplay ,MediaURL
FROM ProductEntries
WHERE Row between
@.startRowIndex and @.StartRowIndex+@.NumRows-1
Friday, March 9, 2012
Pagination recommendations
I am a PHP programmer for a small startup. We are storing person records and our MS SQL Server 2000 database has grown to the point where we wish to paginate the data before returning it to my PHP scripts.
I was wondering if anyone has any recommendations on an optimal way to manage this given the following requirements.
- Data must return only X number of rows at a time (user configurable).
- Must be able to search by several diffent criteria (name, date, birthday, location, ...)
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.Originally posted by shauns
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.
What does that mean?
Did you do a search of this site? Either here or sql team (or both) I know we've bantered this one about a couple of times...|||Here's one link...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33472&SearchTerms=paging
I was wondering if anyone has any recommendations on an optimal way to manage this given the following requirements.
- Data must return only X number of rows at a time (user configurable).
- Must be able to search by several diffent criteria (name, date, birthday, location, ...)
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.Originally posted by shauns
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.
What does that mean?
Did you do a search of this site? Either here or sql team (or both) I know we've bantered this one about a couple of times...|||Here's one link...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33472&SearchTerms=paging
Wednesday, March 7, 2012
PAGEIOLATCH WAIT
Can someone help me understand the various causes of
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues hav
e been disk sub-system driver issues,
raid controller issue and usually anything related to H/W(disk) configuratio
n/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues hav
e been disk sub-system driver issues,
raid controller issue and usually anything related to H/W(disk) configuratio
n/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.
PAGEIOLATCH WAIT
Can someone help me understand the various causes of
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues have been disk sub-system driver issues
raid controller issue and usually anything related to H/W(disk) configuration/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues have been disk sub-system driver issues
raid controller issue and usually anything related to H/W(disk) configuration/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.
Pageiolatch
Can anybody point me in the direction of some information on pageiolatch's?
Can't seem to find much info on them.
thanks
GavThere's not much public info on them right now. MS is working on a white
paper that describes this in more detail.
This type of latch indicates a wait on disk to memory transfers. You can
find out what object is being waited on by looking at the waitresource
column in master..sysprocesses. You'll need to use the dbcc page command to
figure out what the actual object is. The value in waitresource will be of a
format like this:
1:1:140
That's DBID:FileID:PageId.
I don't have the dbcc page command docs handy but you should be able to
google it easy enough.
DBCC PAGE will allow you to figure out what objectId owns the page so you'll
know which object was waiting on the pageiolatch to complete.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpcsdp$6k9$1@.sparta.btinternet.com...
> Can anybody point me in the direction of some information on
pageiolatch's?
> Can't seem to find much info on them.
> thanks
> Gav
>
Can't seem to find much info on them.
thanks
GavThere's not much public info on them right now. MS is working on a white
paper that describes this in more detail.
This type of latch indicates a wait on disk to memory transfers. You can
find out what object is being waited on by looking at the waitresource
column in master..sysprocesses. You'll need to use the dbcc page command to
figure out what the actual object is. The value in waitresource will be of a
format like this:
1:1:140
That's DBID:FileID:PageId.
I don't have the dbcc page command docs handy but you should be able to
google it easy enough.
DBCC PAGE will allow you to figure out what objectId owns the page so you'll
know which object was waiting on the pageiolatch to complete.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpcsdp$6k9$1@.sparta.btinternet.com...
> Can anybody point me in the direction of some information on
pageiolatch's?
> Can't seem to find much info on them.
> thanks
> Gav
>
Subscribe to:
Posts (Atom)