If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
Showing posts with label regarding. Show all posts
Showing posts with label regarding. Show all posts
Wednesday, March 28, 2012
Question Regarding Views and Indexes
If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
question regarding using the sql update command.
Here I have the following command
Dim dtNowAs DateTime = DateTime.Now
SqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=dtnow Where [PK]=@.PK"
What I was ttrying to accomplish was , in my grid view, when someone clicks update, it would automatically set LW to true and set LWD to today's date. No user intervention required. However, I figured the above script would not work. What would I have to do to make LWD = dtnow? I do not want to give the user the option to update anything.
Make it GetDate(). This is sql function to give u current date. No need to use a variable...
in case if u want to use variable
Dim dtNowAs DateTime = DateTime.Now
SqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=' " + dtnow.ToString() + " ' Where [PK]=@.PK"
NICE! Thanks, anyway to clock this 3 hours ahead? The hosting company is 3 hours difference.
|||ya that's correct os till its using my UK location
Question regarding use of a join to the same table
I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my SQL:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my SQL:
> select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> from PS_BOOK A
> right outer join PS_BOOK B
> on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
> A.ASSET_ID = B.ASSET_ID
> where B.BOOK <> 'AMT'
> order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my SQL:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my SQL:
> select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> from PS_BOOK A
> right outer join PS_BOOK B
> on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
> A.ASSET_ID = B.ASSET_ID
> where B.BOOK <> 'AMT'
> order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Question regarding the execution of jobs
We have a ton of jobs that we run from time to time and I am trying to
figure out a way to let me know who executes a job and when. Would anyone
have any ideas they would be willing to share?
Thanks,
Jeff
If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> am trying to
> when. Would anyone
|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>
|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job[vbcol=seagreen]
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
you[vbcol=seagreen]
then[vbcol=seagreen]
configure[vbcol=seagreen]
and I
>
>.
>
sql
figure out a way to let me know who executes a job and when. Would anyone
have any ideas they would be willing to share?
Thanks,
Jeff
If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> am trying to
> when. Would anyone
|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>
|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job[vbcol=seagreen]
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
you[vbcol=seagreen]
then[vbcol=seagreen]
configure[vbcol=seagreen]
and I
>
>.
>
sql
Question regarding the execution of jobs
We have a ton of jobs that we run from time to time and I am trying to
figure out a way to let me know who executes a job and when. Would anyone
have any ideas they would be willing to share?
Thanks,
JeffHi,
Assuming that all jobs are configured with SQL Agent, you
could implement automatic notifications using a MAPI
client. You will have to configure a MAPI client and then
configure the Agent to use that profile and then configure
each job for notification.
hth
DeeJay
>--Original Message--
>We have a ton of jobs that we run from time to time and I
am trying to
>figure out a way to let me know who executes a job and
when. Would anyone
>have any ideas they would be willing to share?
>Thanks,
>Jeff
>
>.
>|||If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> >--Original Message--
> >We have a ton of jobs that we run from time to time and I
> am trying to
> >figure out a way to let me know who executes a job and
> when. Would anyone
> >have any ideas they would be willing to share?
> >
> >Thanks,
> >Jeff
> >
> >
> >.
> >|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
> > Hi,
> >
> > Assuming that all jobs are configured with SQL Agent, you
> > could implement automatic notifications using a MAPI
> > client. You will have to configure a MAPI client and then
> > configure the Agent to use that profile and then configure
> > each job for notification.
> >
> > hth
> >
> > DeeJay
> > >--Original Message--
> > >We have a ton of jobs that we run from time to time and I
> > am trying to
> > >figure out a way to let me know who executes a job and
> > when. Would anyone
> > >have any ideas they would be willing to share?
> > >
> > >Thanks,
> > >Jeff
> > >
> > >
> > >.
> > >
>|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>> Hi,
>> Assuming that all jobs are configured with SQL Agent,
you
>> could implement automatic notifications using a MAPI
>> client. You will have to configure a MAPI client and
then
>> configure the Agent to use that profile and then
configure
>> each job for notification.
>> hth
>> DeeJay
>> >--Original Message--
>> >We have a ton of jobs that we run from time to time
and I
>> am trying to
>> >figure out a way to let me know who executes a job and
>> when. Would anyone
>> >have any ideas they would be willing to share?
>> >
>> >Thanks,
>> >Jeff
>> >
>> >
>> >.
>> >
>
>.
>
figure out a way to let me know who executes a job and when. Would anyone
have any ideas they would be willing to share?
Thanks,
JeffHi,
Assuming that all jobs are configured with SQL Agent, you
could implement automatic notifications using a MAPI
client. You will have to configure a MAPI client and then
configure the Agent to use that profile and then configure
each job for notification.
hth
DeeJay
>--Original Message--
>We have a ton of jobs that we run from time to time and I
am trying to
>figure out a way to let me know who executes a job and
when. Would anyone
>have any ideas they would be willing to share?
>Thanks,
>Jeff
>
>.
>|||If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> >--Original Message--
> >We have a ton of jobs that we run from time to time and I
> am trying to
> >figure out a way to let me know who executes a job and
> when. Would anyone
> >have any ideas they would be willing to share?
> >
> >Thanks,
> >Jeff
> >
> >
> >.
> >|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
> > Hi,
> >
> > Assuming that all jobs are configured with SQL Agent, you
> > could implement automatic notifications using a MAPI
> > client. You will have to configure a MAPI client and then
> > configure the Agent to use that profile and then configure
> > each job for notification.
> >
> > hth
> >
> > DeeJay
> > >--Original Message--
> > >We have a ton of jobs that we run from time to time and I
> > am trying to
> > >figure out a way to let me know who executes a job and
> > when. Would anyone
> > >have any ideas they would be willing to share?
> > >
> > >Thanks,
> > >Jeff
> > >
> > >
> > >.
> > >
>|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>> Hi,
>> Assuming that all jobs are configured with SQL Agent,
you
>> could implement automatic notifications using a MAPI
>> client. You will have to configure a MAPI client and
then
>> configure the Agent to use that profile and then
configure
>> each job for notification.
>> hth
>> DeeJay
>> >--Original Message--
>> >We have a ton of jobs that we run from time to time
and I
>> am trying to
>> >figure out a way to let me know who executes a job and
>> when. Would anyone
>> >have any ideas they would be willing to share?
>> >
>> >Thanks,
>> >Jeff
>> >
>> >
>> >.
>> >
>
>.
>
Question regarding the execution of jobs
Hi,
Assuming that all jobs are configured with SQL Agent, you
could implement automatic notifications using a MAPI
client. You will have to configure a MAPI client and then
configure the Agent to use that profile and then configure
each job for notification.
hth
DeeJay
>--Original Message--
>We have a ton of jobs that we run from time to time and I
am trying to
>figure out a way to let me know who executes a job and
when. Would anyone
>have any ideas they would be willing to share?
>Thanks,
>Jeff
>
>.
>If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> am trying to
> when. Would anyone|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
you[vbcol=seagreen]
then[vbcol=seagreen]
configure[vbcol=seagreen]
and I[vbcol=seagreen]
>
>.
>
Assuming that all jobs are configured with SQL Agent, you
could implement automatic notifications using a MAPI
client. You will have to configure a MAPI client and then
configure the Agent to use that profile and then configure
each job for notification.
hth
DeeJay
>--Original Message--
>We have a ton of jobs that we run from time to time and I
am trying to
>figure out a way to let me know who executes a job and
when. Would anyone
>have any ideas they would be willing to share?
>Thanks,
>Jeff
>
>.
>If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> am trying to
> when. Would anyone|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
you[vbcol=seagreen]
then[vbcol=seagreen]
configure[vbcol=seagreen]
and I[vbcol=seagreen]
>
>.
>
Question regarding storing session with set context_info
Currently our need is to track the data that is changed by the user and record it as a part of the Audit Log.
We have accomplished this need ny making use ofset context_infoand also by passing the UserId from all the Layers and finally log the user name using the set context_info in the stored proc.
What we thought of was using the trigger for each update,delete records for the table.So by setting the context in teh stored proc we could access the UserId in the trigger and able to record the changes made by Tagged userId.
What we want to make sure before making this as our permananet solution is
(1) Reliability - In a web application (N Tier) how would the context not get out of scope.
2) any Performannce related issues, you guys can think of.
3) This Context used in the database -- would that be the part of the connection used from the UI, meaning the if a request is made from UI to the Database . would the context be alive till the connection doesn't go back to the connection pool.
Thanks
Sweety.
No answers yet.
Any idea wether this approach would be good or not.
Need your Input
Thanks
Question regarding stored procs
Hello,
It might be a basic question, but there it goes:
I have a store procedure that first checks if the record exists and, if
it doesn't, inserts the new record.
Is the scenario below possible?
(thread1) USER1 -> check if record "abc" exists
(thread2) USER2 -> check if record "abc" exists
(thread1) USER1 -> "abc" doesn't exist
(thread2) USER2 -> "abc" doesn't exist
(thread1) USER1 -> add "abc" as new record
(thread2) USER2 -> add "abc as new record (OPS, this is an error,
because "abc" already exists, it was just inserted by USER1)
I am wondering if this kind of concurrent, multi-threaded access
happens with stored procedures.
If yes, can I execute a procedure from start to finish without any
other simultaneous procedure interrupting?
I appreciate any information about this.
Leonardo.Hi Leonardo,
The answer to your first question is yes; if you do not establish a
transaction (with the appropriate locking mechanism), then you can have
concurrency issues. However, you can establish a transaction and set
the isolation level to a higher restriction to avoid this scenario.
Check the SQL Server Books OnLine for transactions and transaction
isolation levels; that should get you started.
HTH,
Stu|||(leodippolito@.gmail.com) writes:
> It might be a basic question, but there it goes:
> I have a store procedure that first checks if the record exists and, if
> it doesn't, inserts the new record.
> Is the scenario below possible?
> (thread1) USER1 -> check if record "abc" exists
> (thread2) USER2 -> check if record "abc" exists
> (thread1) USER1 -> "abc" doesn't exist
> (thread2) USER2 -> "abc" doesn't exist
> (thread1) USER1 -> add "abc" as new record
> (thread2) USER2 -> add "abc as new record (OPS, this is an error,
> because "abc" already exists, it was just inserted by USER1)
Yes.
> I am wondering if this kind of concurrent, multi-threaded access
> happens with stored procedures.
> If yes, can I execute a procedure from start to finish without any
> other simultaneous procedure interrupting?
You would need to enclose the IF EXISTS + SELECT in a transaction.
Furthermore, you must make sure that the isolation level is serializable.
The defuault isolation level in SQL Server is READ COMMITTED, which
means that once the EXISTS check has passed, locks are released.
The best solution is to add the table hint "WITH (UPDLOCK)" in the
EXISTS query. This would make USER2 in this example to be blocked
already at this point. If you just use SET TRANSACTION ISOLATION
LEVEL, the two processes will deadlock.
One way to test issues like this, is to insert a WAITFOR in the code,
and then run from separate windows in Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, why do you say " If you just use SET TRANSACTION ISOLATION
LEVEL, the two processes will deadlock." ?
Suppose T1 executes with SERIALIZABLE and T2 tries to execute the same
proc at the same time ... Well, as far as I understand, T2 will wait
for T1 to finish and then go on.. right? Why exactly is " WITH
(UPDLOCK) " necessary?
If it's not a problem for you, could you provide me a safe (deadlock
free) example of insert procedure that would check the existance of the
record before inserting?
Thanks for all.
Leonardo.|||(leodippolito@.gmail.com) writes:
> Erland, why do you say " If you just use SET TRANSACTION ISOLATION
> LEVEL, the two processes will deadlock." ?
> Suppose T1 executes with SERIALIZABLE and T2 tries to execute the same
> proc at the same time ... Well, as far as I understand, T2 will wait
> for T1 to finish and then go on.. right? Why exactly is " WITH
> (UPDLOCK) " necessary?
Because with plain serializable this happens:
T1 performs NOT EXISTS check, and retains a shared lock
T2 performs NOT EXISTS check, and retains a shared lock
T1 tries to insert, but is blocked by T2
T2 tries to insert, but is blocked by T1
=> Deadlock
UPDLOCK is a shared lock, so it does not block other readers. However,
only one process have an UPDLOCK on a resource, so T2 would be blocked
until T1 has committed. And when T2 goes ahead, T2 finds that the rows
is already there, and does not try to insert.
> If it's not a problem for you, could you provide me a safe (deadlock
> free) example of insert procedure that would check the existance of the
> record before inserting?
Hey, that's what I leave as an exercise to the reader. :-) Seriously,
I encourage you to try these things by running from separate windows
in Query Analyzer, and try the various possibilities, to see what deadlocks,
what gives errors and what works smoothly. This is a good lab exercise
to get an understanding of things. What is problematic is to emulate
the concurrency, but some WAITFOR statements are usually good enough.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Question regarding Stored Procedures, Views and ASP
Please could someone explain to me the differences between a stored
procedure and a view.
procedure and a view.
The reason for this question is I have two almost identical ASP pages.
Both get the same results but one uses a stored procedure and one uses
a view. If the query returns no results the 'view page' generates
errors and therefore I have to check for BOF and EOF, whereas the
'stored procedure page' does not generate errors and instead would
appear to return a recordset with 0 entries.
Thanks in advance
Neil.Neil (neil.lloyd@.fdtsolutions.com) writes:
> Please could someone explain to me the differences between a stored
> procedure and a view.
> The reason for this question is I have two almost identical ASP pages.
> Both get the same results but one uses a stored procedure and one uses
> a view. If the query returns no results the 'view page' generates
> errors and therefore I have to check for BOF and EOF, whereas the
> 'stored procedure page' does not generate errors and instead would
> appear to return a recordset with 0 entries.
It might more have to do with who you call them. Could you post the
code, including how you set up cursor type and all? The SP code can
also be useful.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Question Regarding Stored Procedure??OUTPUTS
I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....
Here is the Stored Procedure....
CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword
from
Clinic_Settings
GO
Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter
Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String
cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value
parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value
parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value
parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()
Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.
Question regarding state of processes in sysprocesses table
hi all,
I am having one question releated to processes in sysprocesses table.
What i want to ask is, is it possible that state of sleeping process
can go change to running state.
If yes, then say suppose i am running one time consuming stored
procedure. Then is it possible that once i start execution of this sp
it sill randomly goto running then sleeping or in idle state.
One more question, when the state of any process goes to sleeping, is
it like after execution of process completed running process goes to
sleeping.
And Is it possible that running process goes to idle state.
Because after executing stored procedure and checking sysprocess table
sometime i am getting entry for sp in sysprocess and some times not.
Please help me if i am wrong.
Thanks in advance.Archana (trialproduct2004@.yahoo.com) writes:
> I am having one question releated to processes in sysprocesses table.
> What i want to ask is, is it possible that state of sleeping process
> can go change to running state.
> If yes, then say suppose i am running one time consuming stored
> procedure. Then is it possible that once i start execution of this sp
> it sill randomly goto running then sleeping or in idle state.
> One more question, when the state of any process goes to sleeping, is
> it like after execution of process completed running process goes to
> sleeping.
> And Is it possible that running process goes to idle state.
>
I have a feeling that your problem is not the one you are asking about.
Normally, a "sleeping" process is a connection that is idle and running
any query. But I have seen "sleeping" also for processes that undeniably
are busy doing something. I have not investigated this in detail, but one
thing to watch out for is the ecid column. A spid can spawn several threads,
when parallelism is employed. And in such case, some threads can be sleeping
while waiting for others to complete, I guess.
> Because after executing stored procedure and checking sysprocess table
> sometime i am getting entry for sp in sysprocess and some times not.
Not sure what you mean here, since you are never able to see a stored
procedure as such in sysprocesses. You can of course see the process that
runs it, but if it disconnects after running the procedure, you may not
see it. (Here it's depends on whether connection pooling is in play.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
Thanks for ur reply.
Ya you are right that we never able to see stored procedure in
sysprocesees.
But See i am running only 4 sp. and then i am getting id of that
process and passing that id to dbcc inputbuffer to get information
about stored procedure along with its parameter.
So i can get total number of stored procedure currently running by
looking at process id.
My question is even after executing long running SP, sometime its entry
is displayed in sysprocess table and sometimes not.
Can u tell me why this is happening.
Any help will be truely appreciated.
Thaks in advance.|||Archana (trialproduct2004@.yahoo.com) writes:
> Ya you are right that we never able to see stored procedure in
> sysprocesees.
> But See i am running only 4 sp. and then i am getting id of that
> process and passing that id to dbcc inputbuffer to get information
> about stored procedure along with its parameter.
> So i can get total number of stored procedure currently running by
> looking at process id.
> My question is even after executing long running SP, sometime its entry
> is displayed in sysprocess table and sometimes not.
> Can u tell me why this is happening.
No, because I don't know how you run the procedures. Do you run them
from an application? In such case, what kind of application? How does
the code look like?
Or do you run them from Query Analyzer or Management Studio?
Best would be if you post a script that demonstrates the problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi,
thanks for your reply.
see suppose i have one stored procedure sp_SetId which is accepting one
int parameter say jobid,
So suppose through one application written in c#.net i am executing
this sp using sqlcommand.commandtext like exec sp_setid 1,
and otehr through sql query analysz.
When i see sysprocess table i get two ides with state as runable for
this stored procedure.
say suppose i am getting processid 65 and 66 for these sp. then to get
exactly which jobid is associated with processid i am calling dbcc
inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
get jobid.
This is all what i am doing.
Most of the time this result is proper.
But sometime even if my sp is running its entry is not getting
displayed in sysprocess table i don't know whether that procedure has
started its execution or not.
Can you tell me what am i doing wrong?
Thanks|||Archana (trialproduct2004@.yahoo.com) writes:
> see suppose i have one stored procedure sp_SetId which is accepting one
> int parameter say jobid,
> So suppose through one application written in c#.net i am executing
> this sp using sqlcommand.commandtext like exec sp_setid 1,
> and otehr through sql query analysz.
> When i see sysprocess table i get two ides with state as runable for
> this stored procedure.
> say suppose i am getting processid 65 and 66 for these sp. then to get
> exactly which jobid is associated with processid i am calling dbcc
> inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
> get jobid.
> This is all what i am doing.
> Most of the time this result is proper.
> But sometime even if my sp is running its entry is not getting
> displayed in sysprocess table i don't know whether that procedure has
> started its execution or not.
If the spid is not present in sysprocesses, that spid is obviously
not running the stored procedure. Or any other stored procedure for
that matter. I would be inclined to assume that you are mistaken about
the spid for your procedure.
If all you want do to is to associate spid with jobid, I suggest that
you add a table:
CREATE TABLE spidjobids
(spid int NOT NULL,
CONSTRAINT default_spid DEFAULT @.@.spid,
started datetime NOT NULL.
CONSTRAINT default_spidjobs_started DEFAULT getdate(),
jobid int NOT NULL,
CONSTRAINT pk_spidjobs PRIMARY KEY (spid, started),
CONSTRAINT u_spidjobs UNIQUE (jobid, started)
)
And then modify the procedure to read from that table.
If this not feasible, maybe my procedure aba_lockinfo can be useful.
This procedure gives you the process information and DBCC INPUTBUFFER in
one go. You find it at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I am having one question releated to processes in sysprocesses table.
What i want to ask is, is it possible that state of sleeping process
can go change to running state.
If yes, then say suppose i am running one time consuming stored
procedure. Then is it possible that once i start execution of this sp
it sill randomly goto running then sleeping or in idle state.
One more question, when the state of any process goes to sleeping, is
it like after execution of process completed running process goes to
sleeping.
And Is it possible that running process goes to idle state.
Because after executing stored procedure and checking sysprocess table
sometime i am getting entry for sp in sysprocess and some times not.
Please help me if i am wrong.
Thanks in advance.Archana (trialproduct2004@.yahoo.com) writes:
> I am having one question releated to processes in sysprocesses table.
> What i want to ask is, is it possible that state of sleeping process
> can go change to running state.
> If yes, then say suppose i am running one time consuming stored
> procedure. Then is it possible that once i start execution of this sp
> it sill randomly goto running then sleeping or in idle state.
> One more question, when the state of any process goes to sleeping, is
> it like after execution of process completed running process goes to
> sleeping.
> And Is it possible that running process goes to idle state.
>
I have a feeling that your problem is not the one you are asking about.
Normally, a "sleeping" process is a connection that is idle and running
any query. But I have seen "sleeping" also for processes that undeniably
are busy doing something. I have not investigated this in detail, but one
thing to watch out for is the ecid column. A spid can spawn several threads,
when parallelism is employed. And in such case, some threads can be sleeping
while waiting for others to complete, I guess.
> Because after executing stored procedure and checking sysprocess table
> sometime i am getting entry for sp in sysprocess and some times not.
Not sure what you mean here, since you are never able to see a stored
procedure as such in sysprocesses. You can of course see the process that
runs it, but if it disconnects after running the procedure, you may not
see it. (Here it's depends on whether connection pooling is in play.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
Thanks for ur reply.
Ya you are right that we never able to see stored procedure in
sysprocesees.
But See i am running only 4 sp. and then i am getting id of that
process and passing that id to dbcc inputbuffer to get information
about stored procedure along with its parameter.
So i can get total number of stored procedure currently running by
looking at process id.
My question is even after executing long running SP, sometime its entry
is displayed in sysprocess table and sometimes not.
Can u tell me why this is happening.
Any help will be truely appreciated.
Thaks in advance.|||Archana (trialproduct2004@.yahoo.com) writes:
> Ya you are right that we never able to see stored procedure in
> sysprocesees.
> But See i am running only 4 sp. and then i am getting id of that
> process and passing that id to dbcc inputbuffer to get information
> about stored procedure along with its parameter.
> So i can get total number of stored procedure currently running by
> looking at process id.
> My question is even after executing long running SP, sometime its entry
> is displayed in sysprocess table and sometimes not.
> Can u tell me why this is happening.
No, because I don't know how you run the procedures. Do you run them
from an application? In such case, what kind of application? How does
the code look like?
Or do you run them from Query Analyzer or Management Studio?
Best would be if you post a script that demonstrates the problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi,
thanks for your reply.
see suppose i have one stored procedure sp_SetId which is accepting one
int parameter say jobid,
So suppose through one application written in c#.net i am executing
this sp using sqlcommand.commandtext like exec sp_setid 1,
and otehr through sql query analysz.
When i see sysprocess table i get two ides with state as runable for
this stored procedure.
say suppose i am getting processid 65 and 66 for these sp. then to get
exactly which jobid is associated with processid i am calling dbcc
inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
get jobid.
This is all what i am doing.
Most of the time this result is proper.
But sometime even if my sp is running its entry is not getting
displayed in sysprocess table i don't know whether that procedure has
started its execution or not.
Can you tell me what am i doing wrong?
Thanks|||Archana (trialproduct2004@.yahoo.com) writes:
> see suppose i have one stored procedure sp_SetId which is accepting one
> int parameter say jobid,
> So suppose through one application written in c#.net i am executing
> this sp using sqlcommand.commandtext like exec sp_setid 1,
> and otehr through sql query analysz.
> When i see sysprocess table i get two ides with state as runable for
> this stored procedure.
> say suppose i am getting processid 65 and 66 for these sp. then to get
> exactly which jobid is associated with processid i am calling dbcc
> inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
> get jobid.
> This is all what i am doing.
> Most of the time this result is proper.
> But sometime even if my sp is running its entry is not getting
> displayed in sysprocess table i don't know whether that procedure has
> started its execution or not.
If the spid is not present in sysprocesses, that spid is obviously
not running the stored procedure. Or any other stored procedure for
that matter. I would be inclined to assume that you are mistaken about
the spid for your procedure.
If all you want do to is to associate spid with jobid, I suggest that
you add a table:
CREATE TABLE spidjobids
(spid int NOT NULL,
CONSTRAINT default_spid DEFAULT @.@.spid,
started datetime NOT NULL.
CONSTRAINT default_spidjobs_started DEFAULT getdate(),
jobid int NOT NULL,
CONSTRAINT pk_spidjobs PRIMARY KEY (spid, started),
CONSTRAINT u_spidjobs UNIQUE (jobid, started)
)
And then modify the procedure to read from that table.
If this not feasible, maybe my procedure aba_lockinfo can be useful.
This procedure gives you the process information and DBCC INPUTBUFFER in
one go. You find it at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Question regarding Sql Server memory and cursors
Hello,
We make use of ADO (not ADO.NET) and server-side cursors in one of our products and when viewing Performance Statistics for Sql Server 2005 using Windows Performance Monitor we have noticed that the "Number of active cursor plans" never decreases. In our queries we maintain several open, server-side cursors concurrently. We use .NET/C# and the standard ADO import library. We are closing our recordsets.
The number of active plans jumps by 40 or 50 for each of our queries and as far as we can tell, never decreases (the number of Active Cursors, however, does decrease back to zero.) MSDN/Documentation was somewhat cryptic on what an Active Cursor Plan actually is and Google didn't have any useful information, either.
So, the question is, does this matter? What is an Active Cursor Plan? Is there something other than the recordset and connection that needs to be closed?
Any information or a reference to any useful documentation would be greatly appreciated.
Thanks a lot,
George Kondiles
See this http://blogs.msdn.com/sqlprogrammability/archive/2006/04/27/585170.aspx that says everything.Question regarding SQL Express and Connection Strings
Hello everyone!
I am having some more problems while trying to get my site online. I built the site offline using Visual Web Developer Express Edition, and used "SQL Server Express" that comes with ASP.Net. Therefore, I have the ASPNETDB.mdf and mydatabase.mdf in the App_Data folder. Now, on my local machine everything runs fine. When I tried to put the site online however, I get the following error:
An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26- Error Locating Server/Instance Specified)
This is the connection string in my web.config file:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Now, my web-host does have SQL Server 2005. Does my ASP.Net application use that though, since I have it running just through SQL EXPRESS? Also, does anyone know where I could change my "SQL Server 2005" settings? My web-host uses Plesk 8.2.0, and the database managing program is "ASP.Net Enterprise Manager", but I can't find any options anywhere to change the Server settings to allow remote connections.
Any help would be greatly appreciated! Thanks in advance,
Markuu
You will need to upsize from the sql 2005 express to sql 2005 databases or database.
A good guide to the steps needed is at
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
Hope it helps
|||I don't believe Remote Connections is the issue. The error message clearly states that it could not find the instance of Sql Server that is specified in Data Source part of your connection string - (local)\SQLEXPRESS. That part is the address and name of the Sql Server you are trying to attach to. It's highly improbable that the instance you are using in a hosted environment is called SQLEXPRESS (which is the default name given to instances of Sql Server Express).
I'm not familiar with Plesk or your management system, but usually Web hosting companies will give you an instance that uses something like your user name, or account name. They should also provide a sample connection string and some guidance in a FAQ somewhere. If you can't find it, contact their support.
General connection string related stuff can be found here: www.connectionstrings.com
Question regarding sp_detach
Ran this command - EXEC sp_detach_db 'testlog', 'true'
Deleted the .ldf file associated with the database and
then ran this:
EXEC sp_attach_single_file_db @.dbname = 'testlog',
@.physname = 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_data.mdf'
Then I got this error message
"Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\testlog_log.ldf' may be incorrect.
New log file 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_log.LDF' was created."
Is this an expected error message?
Thanks Mike Allen
Novice DBAMike,
I think the message is more information, letting you know that SQL Server
created a log file for you. Note, however, that BOL states that a new log
file will/can be created only if you actually detached the database first
and only of you have one and only one log file and only one database file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Allen" <mikea@.atg.wa.gov> wrote in message
news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
> Ran this command - EXEC sp_detach_db 'testlog', 'true'
> Deleted the .ldf file associated with the database and
> then ran this:
> EXEC sp_attach_single_file_db @.dbname = 'testlog',
> @.physname = 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_data.mdf'
> Then I got this error message
> "Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
> New log file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_log.LDF' was created."
> Is this an expected error message?
> Thanks Mike Allen
> Novice DBA
>|||Thanks
>--Original Message--
>Mike,
>I think the message is more information, letting you know
that SQL Server
>created a log file for you. Note, however, that BOL
states that a new log
>file will/can be created only if you actually detached
the database first
>and only of you have one and only one log file and only
one database file.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mike Allen" <mikea@.atg.wa.gov> wrote in message
>news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
>> Ran this command - EXEC sp_detach_db 'testlog', 'true'
>> Deleted the .ldf file associated with the database and
>> then ran this:
>> EXEC sp_attach_single_file_db @.dbname = 'testlog',
>> @.physname = 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_data.mdf'
>> Then I got this error message
>> "Device activation error. The physical file
>> name 'D:\Program Files\Microsoft SQL
>> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
>> New log file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_log.LDF' was created."
>> Is this an expected error message?
>> Thanks Mike Allen
>> Novice DBA
>
>.
>sql
Deleted the .ldf file associated with the database and
then ran this:
EXEC sp_attach_single_file_db @.dbname = 'testlog',
@.physname = 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_data.mdf'
Then I got this error message
"Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\testlog_log.ldf' may be incorrect.
New log file 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_log.LDF' was created."
Is this an expected error message?
Thanks Mike Allen
Novice DBAMike,
I think the message is more information, letting you know that SQL Server
created a log file for you. Note, however, that BOL states that a new log
file will/can be created only if you actually detached the database first
and only of you have one and only one log file and only one database file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Allen" <mikea@.atg.wa.gov> wrote in message
news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
> Ran this command - EXEC sp_detach_db 'testlog', 'true'
> Deleted the .ldf file associated with the database and
> then ran this:
> EXEC sp_attach_single_file_db @.dbname = 'testlog',
> @.physname = 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_data.mdf'
> Then I got this error message
> "Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
> New log file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_log.LDF' was created."
> Is this an expected error message?
> Thanks Mike Allen
> Novice DBA
>|||Thanks
>--Original Message--
>Mike,
>I think the message is more information, letting you know
that SQL Server
>created a log file for you. Note, however, that BOL
states that a new log
>file will/can be created only if you actually detached
the database first
>and only of you have one and only one log file and only
one database file.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mike Allen" <mikea@.atg.wa.gov> wrote in message
>news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
>> Ran this command - EXEC sp_detach_db 'testlog', 'true'
>> Deleted the .ldf file associated with the database and
>> then ran this:
>> EXEC sp_attach_single_file_db @.dbname = 'testlog',
>> @.physname = 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_data.mdf'
>> Then I got this error message
>> "Device activation error. The physical file
>> name 'D:\Program Files\Microsoft SQL
>> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
>> New log file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_log.LDF' was created."
>> Is this an expected error message?
>> Thanks Mike Allen
>> Novice DBA
>
>.
>sql
Question regarding SP
Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Hello Patrick,
Inside of T-SQL if you want to get the results back from a stored procedure
you need to insert them into a table as part of running the query.
The syntax for this is INSERT <<table>> (<<cols>> ) EXECUTE <<procedure>>
So for example
INSERT myTable ([Row1], [Row1]) EXECUTE myProcedure
The table you insert these records into can be a temp table, table var, or
even a normal table. Once you have your results in a table you can then crea
te
a cursor using that table.
Aaron Weiker
http://aaronweiker.com/
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows.
> I want to define cursor on the result which is sent by stored
> procedure.
> How can i do that ?
> Thanks
>|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl.
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Hello Patrick,
Inside of T-SQL if you want to get the results back from a stored procedure
you need to insert them into a table as part of running the query.
The syntax for this is INSERT <<table>> (<<cols>> ) EXECUTE <<procedure>>
So for example
INSERT myTable ([Row1], [Row1]) EXECUTE myProcedure
The table you insert these records into can be a temp table, table var, or
even a normal table. Once you have your results in a table you can then crea
te
a cursor using that table.
Aaron Weiker
http://aaronweiker.com/
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows.
> I want to define cursor on the result which is sent by stored
> procedure.
> How can i do that ?
> Thanks
>|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl.
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
Question regarding SP
Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
Thanks
Patrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com
|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>
|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com
|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>
|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
Thanks
Patrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com
|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>
|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com
|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>
|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
Question regarding SP
Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl..
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl..
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
Question regarding SP
Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>
question regarding slow performance an PAGEIOLATCH_SH locks on sql2005
Hi,
I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.
I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.
After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.
This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.
The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.
Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached
to.
The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.
Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.
When i look at the sysprocesses table i am getting various waits as
follows :-
72427200x0042900PAGEIOLATCH_SH 6:9:2192094
72427200x00690SLEEP_TASK
72427200x00000SOS_SCHEDULER_YIELD
The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_YIELD a few times per second.
Running the following to get io stalls gives the following :-
Select * from sys.dm_io_virtual_file_stats (6,7)
Select * from sys.dm_io_virtual_file_stats (6,8)
Select * from sys.dm_io_virtual_file_stats (6,9)
Select * from sys.dm_io_virtual_file_stats (6,10)
gives results like :-
67170853985015624218246512844829457222526431245540454412438340
307010565449074954240x0000000000000954
It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance?
Thanks for any suggestions in advance
Ian.
The SAN setup is not necessarily a problem. Spreading the I/Os over as many
spindles as possible is in general a good approach.
There are three possible causes: (1) your SQL query is not efficient (e.g.
bad plan), (2) you are I/O throughput limited, and (3) the performance is
killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
in this case because you query appears to be dealing a large data set.
I'd first check if an inefficient parallel plan is being used. Assume that
your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
terabyte of data in 2 hours unless the query plan is such that it results in
smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
to you.
You can check several perfmon counters to get a feel whether the I/O
subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
sec/Read. The former tells you how much throughput you are using and the
latter tells you how large is each read. To efficiently process
reporting-type of queries, the latter should be relatively large. Otherwise,
the former will not be good. Ideally, the latter should be larger than 64K
and the fomer should be close to 200MB/sec.
To be sure about what kind of throughput you can get from the drive
presented from your SAN, you should run some tests. If you have a chance, try
a simple table scan on a large and wide table to see how many MBs/sec you
achieve.
BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
Linchi
"ianwr" wrote:
> Hi,
> I wondered in anyone can help with the following problem that i'm
> experiencing, i'll try to provide as much info as possible and any
> suggestions would be appreciated.
> I have just started at an organsiation and there seems to be slow
> performance maybe on the san on a 64bit itanium dual core machine. 4
> CPUs are being showed to sql server, it also has 16gb of RAM. I'll
> start with the configuration of the SAN.
> After speaking to the SAN guy, rather than carve the SAN up into
> different area's for san Logs/Data etc they have gone for the approach
> of spreading a Vdisk across as many spindles as possible (All 145 of
> them). So the area that is presented to the SQL Server according the
> the SAN guys is a vraid 5 stripe made up of all 145 disks which are
> all 72gb fibre-channel disks.
> This storage is not just made available to sql server but also made
> available to other apps as well that need storage. Having read the
> manufactres best practice on setting this up there is a valid argument
> for doing this.
> The bandwidth from the SAN is 2Gb fibre, with each computer that uses
> the SAN having 2Gb fibre cards.
> Clearly, that could act as a bottle-neck. But, there's nothing that
> can be done about it according to the SAN guy.
> Needless to say, any changes on the SAN are pretty much going to be
> out of the question as far as he's concerned but i think performance
> isn't that good for the type of box they have and the SAN its attached
> to.
> The 2nd thing i'll explain is the setup of the database in question,
> firstly whoever set it up split the database into 16 different file of
> 4 filegroups so the table that i'm selecting to is in one filegroup
> split over 4 files and the the table selecting from is in another
> filegroup made up of another 4 files. These are placed on the same
> physical disk made up of the SAN LUN with 145 spindles.
> Anyway when i do a select from a sales table which has various group
> bys and then insert the results into a blank table with no indexes it
> can take over 2hours for 200k rows which i find very slow.
> When i look at the sysprocesses table i am getting various waits as
> follows :-
> 72427200x0042900PAGEIOLATCH_SH 6:9:2192094
> 72427200x00690SLEEP_TASK
> 72427200x00000SOS_SCHEDULER_YIELD
> The process seams to be going inbetween a PAGEIOLATCH and
> SOS_SCHEDULER_YIELD a few times per second.
> Running the following to get io stalls gives the following :-
> Select * from sys.dm_io_virtual_file_stats (6,7)
> Select * from sys.dm_io_virtual_file_stats (6,8)
> Select * from sys.dm_io_virtual_file_stats (6,9)
> Select * from sys.dm_io_virtual_file_stats (6,10)
> gives results like :-
> 67170853985015624218246512844829457222526431245540454412438340
> 307010565449074954240x0000000000000954
> It worries me that when the process is on the PAGEIOLATCH the wait
> can be over 1000. Is it normal for the wait to be this long and what
> would be the best way to prove one way or another if the configuration
> of the san is causing this kind of performance?
> Thanks for any suggestions in advance
> Ian.
>
|||ps. The perf mon stats we are seeing are as follows :-
Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170
|||I think the SAN guy said it was a single 2gb card on the san and that
all servers that accessed the same san also used 2gb cards so he knows
this could be a bottleneck, the entire company uses this san for just
about all server ... i would estimate there must be about 6 MIS type
servers using this SAN 3 live and 3 test plus a number of smaller TP
type systems.
I'll run a few tests on the throughput and let you know
Ian
|||Ok, just done a quick table scan on a large table and the stats were
as follows :-
avg disk bytes/sec approx 76,000 which is on 76k, not exactly the
200mb a sec you had said.
Avg disk sec/read 0.6
I take it these figures aren't too good. Any idea what i can check to
see why they look so bad?
Ian.
|||stats on the tablescan are as follows :-
Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
anything near your 200mb/sec
Avg. Disk sec/Read = 0.6
Any ideas how i can narrow down exactly why the throughput is so bad?
Thanks
Ian.
|||> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
Just noticed a mistake here. What I meant to say is that the I/O block size
(i.e. Avg Disk Bytes/Read) shoud be larger than 64K for reporting queries,
and the throughput (i.e. Avg. Read Bytes/sec) should be close to ~200MB/sec
in the ideal situation (given your setup).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> The SAN setup is not necessarily a problem. Spreading the I/Os over as many
> spindles as possible is in general a good approach.
> There are three possible causes: (1) your SQL query is not efficient (e.g.
> bad plan), (2) you are I/O throughput limited, and (3) the performance is
> killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
> in this case because you query appears to be dealing a large data set.
> I'd first check if an inefficient parallel plan is being used. Assume that
> your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
> terabyte of data in 2 hours unless the query plan is such that it results in
> smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
> to you.
> You can check several perfmon counters to get a feel whether the I/O
> subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
> sec/Read. The former tells you how much throughput you are using and the
> latter tells you how large is each read. To efficiently process
> reporting-type of queries, the latter should be relatively large. Otherwise,
> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
> To be sure about what kind of throughput you can get from the drive
> presented from your SAN, you should run some tests. If you have a chance, try
> a simple table scan on a large and wide table to see how many MBs/sec you
> achieve.
> BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
> Linchi
> "ianwr" wrote:
|||Just to make sure I'm raeding the right numbers. You said 76K/sec and
76K/read. These two numbers don't jive (unless the I/O subsystem is so
horribly bad). So which is which?
Assume that it's 76K/read and the thoughput in terms of bytes/sec is also in
KB's not in MB's. A latency of 0.6 second with a I/O block size of ~76K and
such a low throughput is bad. (BTW, I didn't mean to suggest 200MB/sec for
Avg. Disk Bytes/Read. Rather, I meant to say Avg. Read Bytes/sec,, i.e. the
throughput not the I/O block size. That was a typo in my previous reply).
One way to be 100% sure whether you got a raw deal on the storage is to run
some more specific I/O benchmark tests with IOMeter or sqlio.exe since that
would take SQL Server out of the picture and just focus on the performance of
the disk I/O subsystem. If the numbers are bad from IOMeter or sqlio.exe, you
can use those numbers to confront your SAN folks, and they should not have
any excuses.
Linchi
"ianwr" wrote:
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
>
|||Thanks, will try again on monday then and see where the numbers are.
Will post back on the problem next week, but thanks for the responses
so far anyway.
Ian.
|||Holy crap! 600ms disk sec/read is absolutely horrible! This number on a
properly tuned I/O subsystem sould be 2 ORDERS OF MAGNITUDE lower! The SAN
is clearly overburdened and you will never get adequate performance with
that kind of latency.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:8f28f2c6-179f-4324-8b1b-1ee378b3c157@.y43g2000hsy.googlegroups.com...
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
sql
I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.
I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.
After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.
This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.
The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.
Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached
to.
The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.
Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.
When i look at the sysprocesses table i am getting various waits as
follows :-
72427200x0042900PAGEIOLATCH_SH 6:9:2192094
72427200x00690SLEEP_TASK
72427200x00000SOS_SCHEDULER_YIELD
The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_YIELD a few times per second.
Running the following to get io stalls gives the following :-
Select * from sys.dm_io_virtual_file_stats (6,7)
Select * from sys.dm_io_virtual_file_stats (6,8)
Select * from sys.dm_io_virtual_file_stats (6,9)
Select * from sys.dm_io_virtual_file_stats (6,10)
gives results like :-
67170853985015624218246512844829457222526431245540454412438340
307010565449074954240x0000000000000954
It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance?
Thanks for any suggestions in advance
Ian.
The SAN setup is not necessarily a problem. Spreading the I/Os over as many
spindles as possible is in general a good approach.
There are three possible causes: (1) your SQL query is not efficient (e.g.
bad plan), (2) you are I/O throughput limited, and (3) the performance is
killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
in this case because you query appears to be dealing a large data set.
I'd first check if an inefficient parallel plan is being used. Assume that
your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
terabyte of data in 2 hours unless the query plan is such that it results in
smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
to you.
You can check several perfmon counters to get a feel whether the I/O
subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
sec/Read. The former tells you how much throughput you are using and the
latter tells you how large is each read. To efficiently process
reporting-type of queries, the latter should be relatively large. Otherwise,
the former will not be good. Ideally, the latter should be larger than 64K
and the fomer should be close to 200MB/sec.
To be sure about what kind of throughput you can get from the drive
presented from your SAN, you should run some tests. If you have a chance, try
a simple table scan on a large and wide table to see how many MBs/sec you
achieve.
BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
Linchi
"ianwr" wrote:
> Hi,
> I wondered in anyone can help with the following problem that i'm
> experiencing, i'll try to provide as much info as possible and any
> suggestions would be appreciated.
> I have just started at an organsiation and there seems to be slow
> performance maybe on the san on a 64bit itanium dual core machine. 4
> CPUs are being showed to sql server, it also has 16gb of RAM. I'll
> start with the configuration of the SAN.
> After speaking to the SAN guy, rather than carve the SAN up into
> different area's for san Logs/Data etc they have gone for the approach
> of spreading a Vdisk across as many spindles as possible (All 145 of
> them). So the area that is presented to the SQL Server according the
> the SAN guys is a vraid 5 stripe made up of all 145 disks which are
> all 72gb fibre-channel disks.
> This storage is not just made available to sql server but also made
> available to other apps as well that need storage. Having read the
> manufactres best practice on setting this up there is a valid argument
> for doing this.
> The bandwidth from the SAN is 2Gb fibre, with each computer that uses
> the SAN having 2Gb fibre cards.
> Clearly, that could act as a bottle-neck. But, there's nothing that
> can be done about it according to the SAN guy.
> Needless to say, any changes on the SAN are pretty much going to be
> out of the question as far as he's concerned but i think performance
> isn't that good for the type of box they have and the SAN its attached
> to.
> The 2nd thing i'll explain is the setup of the database in question,
> firstly whoever set it up split the database into 16 different file of
> 4 filegroups so the table that i'm selecting to is in one filegroup
> split over 4 files and the the table selecting from is in another
> filegroup made up of another 4 files. These are placed on the same
> physical disk made up of the SAN LUN with 145 spindles.
> Anyway when i do a select from a sales table which has various group
> bys and then insert the results into a blank table with no indexes it
> can take over 2hours for 200k rows which i find very slow.
> When i look at the sysprocesses table i am getting various waits as
> follows :-
> 72427200x0042900PAGEIOLATCH_SH 6:9:2192094
> 72427200x00690SLEEP_TASK
> 72427200x00000SOS_SCHEDULER_YIELD
> The process seams to be going inbetween a PAGEIOLATCH and
> SOS_SCHEDULER_YIELD a few times per second.
> Running the following to get io stalls gives the following :-
> Select * from sys.dm_io_virtual_file_stats (6,7)
> Select * from sys.dm_io_virtual_file_stats (6,8)
> Select * from sys.dm_io_virtual_file_stats (6,9)
> Select * from sys.dm_io_virtual_file_stats (6,10)
> gives results like :-
> 67170853985015624218246512844829457222526431245540454412438340
> 307010565449074954240x0000000000000954
> It worries me that when the process is on the PAGEIOLATCH the wait
> can be over 1000. Is it normal for the wait to be this long and what
> would be the best way to prove one way or another if the configuration
> of the san is causing this kind of performance?
> Thanks for any suggestions in advance
> Ian.
>
|||ps. The perf mon stats we are seeing are as follows :-
Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170
|||I think the SAN guy said it was a single 2gb card on the san and that
all servers that accessed the same san also used 2gb cards so he knows
this could be a bottleneck, the entire company uses this san for just
about all server ... i would estimate there must be about 6 MIS type
servers using this SAN 3 live and 3 test plus a number of smaller TP
type systems.
I'll run a few tests on the throughput and let you know
Ian
|||Ok, just done a quick table scan on a large table and the stats were
as follows :-
avg disk bytes/sec approx 76,000 which is on 76k, not exactly the
200mb a sec you had said.
Avg disk sec/read 0.6
I take it these figures aren't too good. Any idea what i can check to
see why they look so bad?
Ian.
|||stats on the tablescan are as follows :-
Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
anything near your 200mb/sec
Avg. Disk sec/Read = 0.6
Any ideas how i can narrow down exactly why the throughput is so bad?
Thanks
Ian.
|||> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
Just noticed a mistake here. What I meant to say is that the I/O block size
(i.e. Avg Disk Bytes/Read) shoud be larger than 64K for reporting queries,
and the throughput (i.e. Avg. Read Bytes/sec) should be close to ~200MB/sec
in the ideal situation (given your setup).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> The SAN setup is not necessarily a problem. Spreading the I/Os over as many
> spindles as possible is in general a good approach.
> There are three possible causes: (1) your SQL query is not efficient (e.g.
> bad plan), (2) you are I/O throughput limited, and (3) the performance is
> killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
> in this case because you query appears to be dealing a large data set.
> I'd first check if an inefficient parallel plan is being used. Assume that
> your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
> terabyte of data in 2 hours unless the query plan is such that it results in
> smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
> to you.
> You can check several perfmon counters to get a feel whether the I/O
> subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
> sec/Read. The former tells you how much throughput you are using and the
> latter tells you how large is each read. To efficiently process
> reporting-type of queries, the latter should be relatively large. Otherwise,
> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
> To be sure about what kind of throughput you can get from the drive
> presented from your SAN, you should run some tests. If you have a chance, try
> a simple table scan on a large and wide table to see how many MBs/sec you
> achieve.
> BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
> Linchi
> "ianwr" wrote:
|||Just to make sure I'm raeding the right numbers. You said 76K/sec and
76K/read. These two numbers don't jive (unless the I/O subsystem is so
horribly bad). So which is which?
Assume that it's 76K/read and the thoughput in terms of bytes/sec is also in
KB's not in MB's. A latency of 0.6 second with a I/O block size of ~76K and
such a low throughput is bad. (BTW, I didn't mean to suggest 200MB/sec for
Avg. Disk Bytes/Read. Rather, I meant to say Avg. Read Bytes/sec,, i.e. the
throughput not the I/O block size. That was a typo in my previous reply).
One way to be 100% sure whether you got a raw deal on the storage is to run
some more specific I/O benchmark tests with IOMeter or sqlio.exe since that
would take SQL Server out of the picture and just focus on the performance of
the disk I/O subsystem. If the numbers are bad from IOMeter or sqlio.exe, you
can use those numbers to confront your SAN folks, and they should not have
any excuses.
Linchi
"ianwr" wrote:
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
>
|||Thanks, will try again on monday then and see where the numbers are.
Will post back on the problem next week, but thanks for the responses
so far anyway.
Ian.
|||Holy crap! 600ms disk sec/read is absolutely horrible! This number on a
properly tuned I/O subsystem sould be 2 ORDERS OF MAGNITUDE lower! The SAN
is clearly overburdened and you will never get adequate performance with
that kind of latency.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:8f28f2c6-179f-4324-8b1b-1ee378b3c157@.y43g2000hsy.googlegroups.com...
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
sql
Subscribe to:
Posts (Atom)