Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

GO

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

GO

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

go

AMB

sql

Exec Stored Procedures with high level user permissions

I need to run a stored procedure with admin rights inside the stored procedure like so

Exec xp_cmdshell "c:\start.exe"

i need this to have administrator access so when it is called upon by an asp.net web page that executes the stored procedure it is not run by Network Service user but Administrator. How would i do this.it's the account that runs sqlserver serice that you need|||i am sorry i still don't understand if you could give an example stored procedure or

are you stating that the user that stored the stored procedure is the user level it is run at.|||ohh i forgot to add thank you for replying so fast last time|||what i am saying is that you need to make sure that the account that is used to start sqlserver service is an account that is given enough priveleges to perform the task you need.|||so if the service is started by user Administrator which it alwasy is. That is the user used to start any programs in the stored procedure.

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

Exec SQL Task (stored procedures)

Hi,

Can anyone tell me how to pass parameters from one exec sql task to other ?... (I used stored proc in 1st exec sql task) and passed input parameter (default value set using a variable A) and stored the output parameter value in another variable B.

In the 2nd exec sql task , I passed the output param ( value of B) and doing insert into table xyz...

I get errors (in passing int and string values) . I tried using ole-db as well as ado.net.

Kindly give sample example.

Thanks,

The approach you are using sounds right to me. Could you post the details (including connection manager type) of the 2 execute sql task and the error you get?.

Exec procedures inside other procedures

This should be pretty simple but I am not getting an luck getting what I need by googling.

I am executing a few stored procedures inside of another stored procedure. One of stored procedures returns a value that the other 2 procedures will use how do I get that get and use that variable?

Example

ALTERPROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

BEGIN

this procedure returns an ID

exec ins_procedure1_sp 'Value', 'Value'

these 2 procedures will use the value returned by the above procedure.

exec ins_procedure2_sp id,'Value'

exec ins_procedure3_sp id,'Value'

END

I was going to break this into 2 different procedures. Run ins_procedure1_sp get the value then pass it into ins_mainproc_sp but I was hoping to not make 2 calls.

You can use the OUTPUT parameter here.

Code Snippet

ALTER PROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

Begin

Declare @.Id as int

exec ins_procedure1_sp 'Value', 'Value', @.Id OUTPUT

..

...

....

--Reuse the @.Id on the SP calls

End

Go

Alter Proc ins_procedure1_sp

(

other parameters,

@.Id int OUTPUT

)

as

Begin

Insert ......

Set @.Id = <inserted Value>;

End

Exec procedure on database create/rename/delete ?

Hello,

I need to execute certain procedures automatically when databases are create, renamed or deleted. I looked around in this forum and Internet but couldn't find anything. This is for SQL 2000.

Well, in SQL 2000 it's not as easy as in SQL 2005 where you could use DDL triggers to achieve this.

You could configure different Alerts in SQL Server 2000 to fire when certain messages are logged to the error log, in this case you could try possibly firing an alert when the error message # 1805 fires ("The CREATE DATABASE process is allocating 1.00 MB on disk 'blah'), but that would only capture newly created DBs. You could use the same method for databases that are attached, restored, deleted, etc., you'd just have to be sure to get error messages that are fired for each 'event'. Once you have the appropriate Alert configured, just set the alert to execute a SQL Agent job when it fires, and setup the Agent job to run the SQL code you want.

If you don't need the code to run exactly when the DB is created/deleted/attached/etc., you could run a trace that captures the appropriate events, then load the trace file nightly and scan for the appropriate event classes, then executing code appropriately as needed.

HTH,

|||

Thanks a lot Chad. This is very helpful. I didn't think about alerts.

sql

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. Now
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid giving
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advance
Hi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e.
dropping tables, etc).
HTH,
Adam
"David" wrote:

> I have a user in DB that has db_datareader and db_datawriter permissions. Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader &
> db_datawriter and map it to a login?
> Thanks in advance
|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance
|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah
|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegro ups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>
|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. Now
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid giving
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advanceHi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e.
dropping tables, etc).
HTH,
Adam
"David" wrote:
> I have a user in DB that has db_datareader and db_datawriter permissions. Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader &
> db_datawriter and map it to a login?
> Thanks in advance|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegroups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. No
w
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid givin
g
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advanceHi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e
.
dropping tables, etc).
HTH,
Adam
"David" wrote:

> I have a user in DB that has db_datareader and db_datawriter permissions.
Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giv
ing
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
&
> db_datawriter and map it to a login?
> Thanks in advance|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegroups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

exec permision for sql user

I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:

select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
from INFORMATION_SCHEMA.ROUTINES|||hey, thanks for the information.
what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.|||Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.

EXEC of a sproc within another sproc

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy

Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@.EntryLogID int,
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2)
)
AS
DECLARE@.OldCategoryID int

EXEC @.OldCategoryID = BP_GetLedgerCategory @.EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @.ProjectID,
CategoryID = @.NewCategoryID,
Expended = @.Expended

WHERE
EntryLogID = @.EntryLogID

EXEC BP_UpdateCategories @.ProjectID, @.NewCategoryID, @.Expended, @.OldCategoryID

Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@.EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @.EntryLogID

RETURN

*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2),
@.OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.NewCategoryID

UPDATE
BP_Categories
SET CatExpended = CatExpended - @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.OldCategoryIDyou need to use OUTPUT parameters to return a value..check BOL (Books On Line ) its a free download from microsoft...check for correct synax and documentation...

hth|||Thank you...though...I found more info later last night after I came across the phrase 'OUTPUT' parameters.

This thread in particular ...
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=399995

My sproc is finished and I've moved on...

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2.How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.
Have a look at these:
http://support.microsoft.com/default...;en-us;Q243586
http://www.sql-server-performance.co...recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://www.sql-server-performance.c..._recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

ExceptionHandling in T-SQL?

Hi all,

Is there any concept of exception handling in T-SQL while writing Stored Procedures, plz. help me with this issue. I'm using SQL Server 2000

Hello Trid,

No, there is no structural error handling in 2000 so to speak. There are several ways to perform error handling in 2000, with the most common being to test the value of @.@.ERROR for a non-zero value, then send control to a handler lable for rollback etc.

if (@.@.error <> 0)

goto error_handler

return (0)

error_handler:

if (@.@.trancount > 0)

rollback tran

return (-1)

Cheers

Rob

|||

To back up what Robert said, T-SQL did not have a rich set of error handling capabilities in 2000, whatsoever. You have to deal with the error message on the client end. So be careful to check for and close any transactions you might have started when you get error messages back and want to stop the batch.

|||

Thank you Robert :)

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROMQuote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
Ruz
Ruz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>
|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:

> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> graphically?!?!
> END),0)
>
>
|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"[vbcol=seagreen]
>
> Cheers,
> "Uri Dimant" wrote:
date[vbcol=seagreen]
this[vbcol=seagreen]

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:

> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> graphically?!?!
> END),0)
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"[vbcol=seagreen]
>
> Cheers,
> "Uri Dimant" wrote:
>
date[vbcol=seagreen]
this[vbcol=seagreen]

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:
> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > Hi,
> >
> > I'm trying to run a sproc that was created on SQL server and has two date
> > parameters. I can run the sproc easily enough with fixed values but it
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
> >
> > I have seen that the suggested solution is to create a view but as this
> > query uses group by, using the where clause in a view to specify the
> > parameteres will not give the correct result set.
> >
> > This is part of the SQL from the sproc.....
> >
> > SELECT SG.SchemeName as SchemeName,
> > 'Fail' as Type,
> > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> END),0)
> > as Value
> > FROM Quote Q
> > INNER JOIN SchemeGroup SG
> > ON Q.SchemeGroupID = SG.ID
> > INNER JOIN SubScheme SS
> > ON Q.ID = SS.QuoteID
> > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > GROUP BY SG.SchemeName
> > ORDER BY SG.SchemeName,Type desc
> >
> > Any help or ideas would be appreciated.
> >
> > Thanks,
> >
> > Ruz
> >
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"
>
> Cheers,
> "Uri Dimant" wrote:
> > Ruz
> > CREATE PROC myProc
> > @.SDate DATETIME,
> > @.FDate DATETIME
> > AS
> > --Your code here
> >
> >
> > If not sure I understand you
> > What does it mean?
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> >
> >
> >
> > "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> > news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > > Hi,
> > >
> > > I'm trying to run a sproc that was created on SQL server and has two
date
> > > parameters. I can run the sproc easily enough with fixed values but it
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> > >
> > > I have seen that the suggested solution is to create a view but as
this
> > > query uses group by, using the where clause in a view to specify the
> > > parameteres will not give the correct result set.
> > >
> > > This is part of the SQL from the sproc.....
> > >
> > > SELECT SG.SchemeName as SchemeName,
> > > 'Fail' as Type,
> > > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> > END),0)
> > > as Value
> > > FROM Quote Q
> > > INNER JOIN SchemeGroup SG
> > > ON Q.SchemeGroupID = SG.ID
> > > INNER JOIN SubScheme SS
> > > ON Q.ID = SS.QuoteID
> > > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > > GROUP BY SG.SchemeName
> > > ORDER BY SG.SchemeName,Type desc
> > >
> > > Any help or ideas would be appreciated.
> > >
> > > Thanks,
> > >
> > > Ruz
> > >
> >
> >
> >