How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups
.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>sql

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
Thanks
SET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?
|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do I install sqlcmd.exe in my SQL Server client

I have SQL Server 2000 client installed in my machine and I need to call stored procedures thru sqlcmd utility. When I tried using this utility, I got an error message from command prompt sqlcmd command does not exist.

I have osql utility in my client version, but i need to have sqlcmd utility.

Please advice how do I install sqlcmd.exe in my SQL Server client?SQLCMD was introduced in SQL Server 2005. For SQL Server 200, you can either use the OSQL.exe or the ISQL.exe tool.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I handle time-zones?

I need to be able to consider time-zones, including daylight savings, within
stored procedures. Can anyone point me to a resource/code base which will
either allow me to procure or build my own code? I need to do things like
determine local time based on a customer's state, zip code, city, or whateve
r
is required to truly determine the customer's local time.
Thanks,
Michael"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael
You are in for a heap of work. Especially if you need to go international.
Just a couple of notes. Daylight savings time is done differently in
different countries. IIRC, the US currently switches on the last Sunday in
October and the first Sunday in April. England switches on the last Sunday
in October and the last Sunday in March.
Some countries require a minium of 150 days of daylight savings time, so
they have different schedules every year.
If you only need to do the US, then you will need to create a stack of
lookup tables for state, zip codes and cities. You will need logic to
default if the zip code or city doesn't exist. You will need to keep in
mind also that some states like Tennesse and Texas have multiple time zones.
You will need to keep in mind that Arizona and Hawaii don't do daylight
savings time and part of Indiana doesn't either.
Google for daylight savings time and you will find a number of articles
regarding this issue.
Good luck!
Rick Sawtell|||> Just a couple of notes. Daylight savings time is done differently in
> different countries. IIRC, the US currently switches on the last Sunday
> in October and the first Sunday in April.
And to add to the fun, that changes next year!
A|||I just posted this the other day for similar question here:
http://channel9.msdn.com/ShowPost.aspx?PostID=142586
Client would still need to pass it's standard time zone name to the proc or
you could have that data stored in a table by user.
William Stacey [MVP]
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael

How Do I give EXECUTE Permissions on Stored Procedures?

Hey guys,

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!alvinstraight38@.hotmail.com wrote:

Quote:

Originally Posted by

Hey guys,
>
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>

if you right click on the SP and click properties, that should bring up
the options.

--sharif|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

Then again, in the long run you are better of using GRANT commands.

--
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|||On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Then again, in the long run you are better of using GRANT commands.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00

That's SP1.

--
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|||On Sep 20, 10:26 pm, "alvinstraigh...@.hotmail.com"
<alvinstraigh...@.hotmail.comwrote:

Quote:

Originally Posted by

On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
>
>

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>

Quote:

Originally Posted by

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

>

Quote:

Originally Posted by

Then again, in the long run you are better of using GRANT commands.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
- Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')|||abu hisham wrote:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.|||abu hisham (yjogee@.hotmail.co.uk) writes:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
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

How do I get a Fields List with Stored Procedures

If I type in a query I get a fields list. However I do not with a stored
procedure. I have tried pressing the Refresh button. This does not work. I
have tried entering the fields manually, but when I get to the value column,
the expression builder tells me that the dataset has no fields. I can run
the stored procedure in Reporting Services and get the correct results. I
just cannot figure out how to get a dataset to see the fields in a stored
procedure.
I've been trying everything I can think of for two days. Does anyone out
there know how to get the fields to show with a stored procedure?The value will be the same as the field name and the type is databasefield
so for example:
fieldname type value
CREW DatabaseField CREW
AND SO ON
You can't use the expression to pick fields because you have not entered the
fields manually. I find I have to enter my fields manually if I have temp
tables used in my stored proc
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||They should show automatically when you execute the dataset on the Data tab.
Are you sure you have a command type of 'storedprocedure'?
Worst case scenario try deleting your dataset and recreating it.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:6C363085-B23B-4B5B-AC32-1C0C1DA4AF3F@.microsoft.com...
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work.
> I
> have tried entering the fields manually, but when I get to the value
> column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||I have tried that. I have also tried right-clicking on the fields list pane
and adding fields. Either way when I try to pull the fields into the report,
they get put in there as SUM (Fields.fieldname.value). When I edit the sum
function away, I get an error saying that the field is not in the dataset.
When I hit Refresh, the fields disappear.
There are no temp tables or table variables in the stored procedure. It
returns one resultset.
How do I delete a dataset?
"John Grant" wrote:
> The value will be the same as the field name and the type is databasefield
> so for example:
> fieldname type value
> CREW DatabaseField CREW
> AND SO ON
> You can't use the expression to pick fields because you have not entered the
> fields manually. I find I have to enter my fields manually if I have temp
> tables used in my stored proc
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I fought with the same issue for 2 days when first working with sprocs in
rs. after you set the command type to stored procedure, run it it once from
the data window. Then refresh it. that did the trick for me. if you have
parameters, don't forget to include them as well.
--
John Cleveland
Network Manager
Urban systems Ltd
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||mmmmmmmmmmm, do you have set nocount on inside your stored proc?
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have the button to delete dataset while in the data tab
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have several good suggestions. Here is a list:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
5. If your stored procedure calls another stored procedure then try this:
add Set FMTONLY Off (the following is from Simon Sabin a SQL Server MVP).
"The issue with RS is that the rowset of the SP is defined by calling the SP
with SET FMTONLY ON because Temp tables don't get created if you select from
the temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."
One of the 5 above should solve your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:59D6F2CC-1CE4-450F-9D55-74B724CB5AAF@.microsoft.com...
>I have tried that. I have also tried right-clicking on the fields list
>pane
> and adding fields. Either way when I try to pull the fields into the
> report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the
> sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
>> The value will be the same as the field name and the type is
>> databasefield
>> so for example:
>> fieldname type value
>> CREW DatabaseField CREW
>> AND SO ON
>> You can't use the expression to pick fields because you have not entered
>> the
>> fields manually. I find I have to enter my fields manually if I have
>> temp
>> tables used in my stored proc
>> "Liz" wrote:
>> > If I type in a query I get a fields list. However I do not with a
>> > stored
>> > procedure. I have tried pressing the Refresh button. This does not
>> > work. I
>> > have tried entering the fields manually, but when I get to the value
>> > column,
>> > the expression builder tells me that the dataset has no fields. I can
>> > run
>> > the stored procedure in Reporting Services and get the correct results.
>> > I
>> > just cannot figure out how to get a dataset to see the fields in a
>> > stored
>> > procedure.
>> >
>> > I've been trying everything I can think of for two days. Does anyone
>> > out
>> > there know how to get the fields to show with a stored procedure?|||I'm afraid that none of this is working. I deleted the dataset and recreated
it. I commented out the "Set Nocount On". I ran the stored procedure and
got the result set. I pressed Refresh. No good.
Any other ideas?
"John Cleveland" wrote:
> Liz,
> I fought with the same issue for 2 days when first working with sprocs in
> rs. after you set the command type to stored procedure, run it it once from
> the data window. Then refresh it. that did the trick for me. if you have
> parameters, don't forget to include them as well.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I know how frustrating it was for me to get it going as well. So if you
would like you can contact me dierctly. I'm not with MS at all, but do know
how to get it to work. Use my contact info below if you wish.
--
John Cleveland
Network Manager
Urban systems Ltd
office: (250) 762-2517
jcleveland@.urban-systems.com
"Liz" wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
> >
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||On Apr 12, 6:02 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
>
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> > "Liz" wrote:
In your SP make a select * into temptable from ...
and then in the last line write select * from temptable
When you do that if you refresh the fields appear, after that just
remove the into command and the "select * from temptable" but you
can't refresh otherwise the fields disappear.
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure... Hide quoted text -
> - Show quoted text -