Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub

If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.

When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.

Can someone suggest how I can get the value 0-6 to my code?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

executing package DTExec

I created a package and stored in SQL server.

I am running it as follows

master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"

ABC DEF GHI --> is my package name.

When i run this from a job, it runs without error

but when i run the above sql i am getting error as

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '/'.

Am i missing some thing, please advice. Is that the problem between spaces in the package name?

I missed the single quotes

master.dbo.xp_cmdshell 'dtexec /sq "ABC DEF GHI" /ser "Prod"'

Executing a stored proc on another server from a Scheduled task

Ok, I thought this one would be easy.

I have a stored proc: master.dbo.restore_database_foo

This is on database server B.

Database server A backs up database foo on a daily basis as a scheduled
task.

What I wanted to do was, at the end of the scheduled task is then call the
stored proc on B and restore the database.

If I go into Query Analyzer and log into database A, then exec
b.master.dbo.restore_database_foo works.

But if I take the same command and make it part of the scheduled task it
fails.

Error is:

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312). The step failed.

To me this seems like a permissions issue, but nothing I've tried seems to
have helped.

Suggestions?

--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.comGreg D. Moore (Strider) (mooregr@.greenms.com) writes:
> I have a stored proc: master.dbo.restore_database_foo
> This is on database server B.
> Database server A backs up database foo on a daily basis as a scheduled
> task.
> What I wanted to do was, at the end of the scheduled task is then call the
> stored proc on B and restore the database.
> If I go into Query Analyzer and log into database A, then exec
> b.master.dbo.restore_database_foo works.
> But if I take the same command and make it part of the scheduled task it
> fails.
> Error is:
> OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error
> 7399) [SQLSTATE 01000] (Error 7312). The step failed.
> To me this seems like a permissions issue, but nothing I've tried seems to
> have helped.

SELECT * FROM master..sysmessages where error in (7399, 7312) gives me

"Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part
name was supplied, but the provider does not expose the necessary interfaces
to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
error. %ls"

Doesn't tell me a whole lot.

Can't you take the easy way out and make the step a command-line
step that invokes OSQL?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
four-part
> name was supplied, but the provider does not expose the necessary
interfaces
> to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> error. %ls"
> Doesn't tell me a whole lot.

Yeah, same here. It seems to be one of those things that SHOULD be easy.
:-)

> Can't you take the easy way out and make the step a command-line
> step that invokes OSQL?

I could, but that just seems to "dirty" :-)

But that may be the way I go if I can't find a cleaner solution.

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<wM95b.29715$yG2.21729@.twister.nyroc.rr.com>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> > SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> > "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
> four-part
> > name was supplied, but the provider does not expose the necessary
> interfaces
> > to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> > error. %ls"
> > Doesn't tell me a whole lot.
> Yeah, same here. It seems to be one of those things that SHOULD be easy.
> :-)
> > Can't you take the easy way out and make the step a command-line
> > step that invokes OSQL?
> I could, but that just seems to "dirty" :-)
> But that may be the way I go if I can't find a cleaner solution.
>
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp

If you are restoring database remotely you should give local admin
privileges on target server to the SQLServerAgent from the primary
server. Just executing stored procedure from the Query Analyzer does
not simulate fully your situation. You should assign one Global
Windows-based account to the SQLServerAgent on primary server and give
it local admin privileges on target server instead of using generic
Local System account. If Network Admins are not cooperative you can
create Local Windows accounts with the same name and password on both
servers and add these accounts to both database servers.

Sinisa Catic

ExecuteSQL Fails from Variable

I am executing the following statement to setup a database:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.

I get this error:

SSIS package "BuildTemplates.dtsx" starting.

Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.

Thanks,

execute stored procedure

I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!

a stored procedure is run under the security context of who ran the Sps. When the sp is run it is executed in the context of who ever run the Sps. if you deny the user on the base table. th sps will fail.

how about a view with an unbroken ownership chain.

how about using functions instead of Sp.

just a wild guess....

|||

Alternatives that you can use in SQL Server 2005:

- sign the procedure and grant permission to access the tables to the certificate used for signing

- use an EXECUTE AS clause for the procedure to make it execute under a different execution context.

For signing, I have an example at: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

For EXECUTE AS, see documentation at:

http://msdn2.microsoft.com/en-us/library/ms187926.aspx

Thanks
Laurentiu

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

"

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

"

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.

EXECUTE permission denied on object Test, database DI, owner dbo

hi,
I use DataGrid in name UserTable.
I use this code:

SqlCon.Open()

UserTable.DataSource = SqlCom.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

UserTable.DataBind()

and i get this error:EXECUTE permission denied on object 'Test', database 'DI', owner 'dbo'
I craete stored prcedure in name Test with the simple SQL code:
CREATE PROCEDURE [dbo].[Test] AS
select Users.*
from Users
GO

If instead i put the SQL code :select Users.* from Users
in my command as a text i get the error :SELECT permission denied on object 'Users', database 'DI', owner 'dbo'

i have already create a local premmision for my DB & tables as MYMACHINE/ASPNET

How i can solve this problem?
Thanks, Moshe

It is definatly a database permissions issue. A quick and dirty fix is to give the aspnet account database owner permissions to the database DI, this is not recommended for a release version of the application.

With SPROCS what you need to do (if the account isnt in the owner group of sql) then you need to specify that the account has execute permissions for each sproc.

This can be achieved by going into enterprise manager, finding said sproc, right clicking on it, goto permissions (i think) and add the correct user to execute.

Hope this helps,

Andrew

|||There are two permissions in SQL Server you may have created the database permissions by right click on user in the database, now go to the Security section in Enterprise Manager and Create the Server login. Hope this helps.

EXECUTE permission denied on object MySPorDB, database MyDatabase, owner dbo.

I just installed this application on my new server and I get this error for each object in the database including the tables and the stored procedures. I have been going in for each one and opening the properties and then going to permissions and making the changes for each one.
Is there a way to do this for the entire database at once?
ThanksYou can scrpt this using a SQL script that can be executed in the SQL Query Analyzer. Beside of that, security can be set through "roles" for a user on a database level (for instance, if a user is a db_owner - which is not secure to do of course!!! - it will have these rights by default on the objects).|||It is for a database I will be using over and over, so I will probably try to work it into the sql scripts.

Thanks
Greg

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
Q
If you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:
>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

EXECUTE AT <Linked Server> Question

In a stored procedure, I have the following TSQL statement:

EXECUTE ('Insert Into Federation.dbo.FederationData (FederationData) Values
(?)', @.Buffer) AT [HostBox.abcd.mydomain.com\SQLServer2005,1563];

This works fine. However, I do not want to hard-code the name of the linked server like I have above. I would like to specify it at run-time (because it could be different based on certain situations). I cannot seem to get it to work using a variable to represent the linked server name, so it can be dynamic. Can it be done?

Thanks - Amos

What about something like the following:

DECLARE @.SQL1 varchar(500)

DECLARE @.STR_LINKEDSERVER varchar(80)

SET @.STR_LINKEDSERVER=N'HostBox.abcd.mydomain.com\SQLServer2005,1563'

SET @.SQL1 = N'EXECUTE (''Insert Into Federation.dbo.FederationData (FederationData) Values (?)'', @.Buffer) AT [' + @.STR_LINKEDSERVER + '];'

EXECUTE sp_executesql @.SQL1

Execute as Caller

This is a followup to my previous question.
Example SP:
CREATE PROCEDURE [dbo].[ChangeWorkspace]
@.UserName varchar(32),
@.Workspace varchar(32)
WITH EXECUTE AS CALLER
AS
BEGIN
update table1
set workspace = @.Workspace
where username = @.Username;
END
In the DB table1 is in a schema: user1 (user1.table1)
I connect via ASP.Net with a simple connection string that logs in with User
Id= user1:
In Asp.Net I run the Stored procedure
cmd.ExecuteNonQuery();
The error is:
System.Data.SqlClient.SqlException: Invalid object name 'table1'.
***so I assume its looking for dbo.table1 -- now if in my sp I use the
schema prefix it works:
update user1.table1s
set workspace = @.MapWorkspace
where username = @.Username;
So the problem is why doesn't the SP act as if I did user1.table1 since in
the SP I have WITH EXECUTE AS CALLER
Thanksdev648237923 (dev648237923@.noemail.noemail) writes:
> So the problem is why doesn't the SP act as if I did user1.table1 since in
> the SP I have WITH EXECUTE AS CALLER
Because the procedure uses the default schema of the procedure owner.
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 dev648237923,
I agree with Erland that the problem you met is caused by the "dbo" schema
name of the stored procedure, the stored procedure's code will inherit the
schema context from the procedure's schema. Actually stored procedure is
designed to be schema specific, therefore, when we specify a schema name
for one sp, its contained T-SQL code will also followup that schema
context. While the "EXECUTE AS" is mainly for security context purpose.
For your scenario, the target table's schema name should match the stored
procedure's schema name , like:
CREATE PROCEDURE [user1].[ChangeWorkspace]
...............
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi dev648237923,
Have you got any further ideas on this issue? If there is still anything we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)