Executing Remote Stored Procedures in Triggers

Hi,

I've been scratching my head over this problem for quite a while. I have two SQL SERVER 2005 servers running on the same network, lets call them ServA and ServB. ServB is configured as a linked server on ServA as LinkedServB. On ServA, there is a database called DatabaseA, in which there is a table called TableAA, on which I wrote a trigger on delete.

In that trigger, I want to update a table, lets call it TableBB, in DatabaseB on ServB.

So the trigger looks like this:

CREATE TRIGGER triggerAfterDelete
ON TableAA
AFTER DELETE
AS
BEGIN

SET NOCOUNT ON;

Update [LinkedServB].[DatabaseB].[dbo].[TableBB]
set [SomeColumn] = 'SomeValue'
where [SomeOtherColumn] = 'SomeOtherValue'

END

When I delete something from TableAA, the trigger fires, and on trying to update, an error is raised which is:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Now the same query works as a separate query. But inside the trigger it does not. I've tried to use try-catches, nested transaction, named transaction, saving transactions, distributed tran, checking @.@.error, running the query using openquery, running the query using sp_executesql, but they have all given me some error or other.

Selects in the trigger work fine, but updates, inserts and deletes do not work.
And like I mentioned, as a stand alone query, they work fine, as a query, in openquery, or in sp_executesql

Any help would be much appreciated.

Thanks in Advance

Vinit Pandya
Senior .NET Developer

You need to SET XACT_ABORT ON to avoid the error. Otherwise the provider has to support nested transactions for this to work and SQL Server does not support nested transactions. For details on the behavior of distributed queries in transactions, see the BOL topic below:

http://msdn2.microsoft.com/en-us/library/aa213080(SQL.80).aspx

|||Hi Umachandar,

Thanks for your reply. I've actually tried that too, and also other settings which I read helped other people such as:

SET XACT_ABORT ON;
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

But still no luck. Any other ideas ?

Thanks.

Vinit Pandya
Senior .NET Developer

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"
|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>

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 DTS package from VB.NET

Hello,

I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:

Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)\n

I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?

Any ideas?

BrianHey,

It seems the error is because the steps aren't being executed in the right order. The steps are being sorted by name so to speak, as the execute SQL's are being fired first, then the dynamic task, then the pump tasks, etc. Is there anyway around that, either in the package or through code?

Brian|||The tasks in a DTS package will attempt to execute all at once unless you've established a workflow. With a workflow, the next task won't execute until the previous task has completed. Try this MSDN article and see if you can locate what you're missing:DTS Package Workflow.

Terri

Executing DTS Package - Error not able to find the package.

I am running Sqlserver 2005 and IS.
Here is the SP I am running.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: Name
-- Create date:
-- Description:
-- ========================================
=====
ALTER PROCEDURE [dbo].[CallRiskDataDTS]
-- Add the parameters for the stored procedure here
@.loaddate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.DTSPackageObject int
DECLARE @.HRESULT int
DECLARE @.property varchar(255)
DECLARE @.return int
DECLARE @.ErrDescrip varchar(255)
DECLARE @.ErrSource varchar(30)
DECLARE @.ErrHelpId int
DECLARE @.ErrHFile varchar(255)
DECLARE @.ErrMsg varchar(255)
Declare @.FileFilter varchar(8)
DECLARE @.sDTSPackagePath varchar(1000)
DECLARE @.sDTSSpecialUser varchar(50)
if not IsDate(@.loaddate) = 0
Begin
select @.loaddate = getdate()
End
select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(mm,@.loaddate)))),'') +
convert(varchar(2), Datepart(mm,@.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(dd,@.loaddate)))),'') +
convert(varchar(2), Datepart(dd,@.loaddate))
SELECT @.ErrMsg = 'Error running DTS package'
-- Create a DTS Package object
EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR (@.ErrMsg,11,1)
RETURN
END
EXEC @.HRESULT = sp_OAMethod
@.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolData
Upload",
Nothing)', NULL --, @.flags=256,
@.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
@.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
@.PackagePassword =''
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
OUTPUT,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrDescrip,11,1)
RETURN
END
-- Set the FailOnError property to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Set
to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
@.FileFilter --Set to true
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
-- Call the EXECute method
EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
--remove the object from memory
EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
I am getting the following message
Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
{}.{}') does not exist. I want to set this up so I can execute is from an
ASP.NET page. I am unable to find the cause of it.
In my SQL Management Studio, connect to Integration services, I see the
package in STORED PACKAGES , MSDB tree.
Any help is greatly appreciated.
ThanksFor that matter I am not able execute any package, I am getting the same
error. Thanks
Raj.
"Raj25" wrote:

> I am running Sqlserver 2005 and IS.
> Here is the SP I am running.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
> -- ========================================
=====
> -- Author: Name
> -- Create date:
> -- Description:
> -- ========================================
=====
> ALTER PROCEDURE [dbo].[CallRiskDataDTS]
> -- Add the parameters for the stored procedure here
> @.loaddate datetime
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @.DTSPackageObject int
> DECLARE @.HRESULT int
> DECLARE @.property varchar(255)
> DECLARE @.return int
> DECLARE @.ErrDescrip varchar(255)
> DECLARE @.ErrSource varchar(30)
> DECLARE @.ErrHelpId int
> DECLARE @.ErrHFile varchar(255)
> DECLARE @.ErrMsg varchar(255)
> Declare @.FileFilter varchar(8)
> DECLARE @.sDTSPackagePath varchar(1000)
> DECLARE @.sDTSSpecialUser varchar(50)
> if not IsDate(@.loaddate) = 0
> Begin
> select @.loaddate = getdate()
> End
> select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(mm,@.loaddate)))),'') +
> convert(varchar(2), Datepart(mm,@.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(dd,@.loaddate)))),'') +
> convert(varchar(2), Datepart(dd,@.loaddate))
> SELECT @.ErrMsg = 'Error running DTS package'
> -- Create a DTS Package object
> EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR (@.ErrMsg,11,1)
> RETURN
> END
> EXEC @.HRESULT = sp_OAMethod
> @.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolDa
taUpload",
> Nothing)', NULL --, @.flags=256,
> @.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
> @.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
> @.PackagePassword =''
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
> OUTPUT,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrDescrip,11,1)
> RETURN
> END
> -- Set the FailOnError property to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Se
t
> to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
> @.FileFilter --Set to true
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> -- Call the EXECute method
> EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> --remove the object from memory
> EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
> END
>
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
>
> I am getting the following message
> Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
> The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
> {}.{}') does not exist. I want to set this up so I can execute it from a
n
> ASP.NET page. I am unable to find the cause of it.
> In my SQL Management Studio, connect to Integration services, I see the
> package in STORED PACKAGES , MSDB tree.
> Any help is greatly appreciated.
> Thanks
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

Executing batch script from MSSQL

If I have an application running on MSSQL is it possible that if an action occurs on the application side that a stored procedure can execute a batch script?xp_cmdshell. requires certains permissions. it is likely whatever programming language you are using, also has a command for command line and it would probably be better to do it from the application instead of the database.|||OK, so probably since the application uses asp I could call a script from this side, right?|||OK, so probably since the application uses asp I could call a script from this side, right?

ASP has it's own little peculiar security issue. The package has to be executed from the security context of the IUSR_XXXX NT account that executes the pages on the web server. If this is'nt done right you can create a security problem.

Do a search on this forum using ASP and DTS as terms.|||I suggest avoiding xp_CmdShell - outta process can mean inna trouble.

What you want to do is a bit quirky, and there may be a better solution if you were to provide more details (for example, why are you seeking a synchronous solution? and what exactly is that batch script doing?).

Outta band is outta sight!

Executing a whole container in one transaction

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

does the dataflow task get "stuck" if you execute it by itself?

Note that some operation in dataflow - caching for lookup - may take a long time - so how long does it stay stuck ?
|||

Liran R wrote:

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

|||Liran,

Is the behaviour same when you set the TransactionOption to "Supported" or "Not Supported", i.e. when not using DTC?

Also, take a look at the data flow window for that DFT to see which tasks are in yellow.|||

Jamie Thomson wrote:

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

Yes, there is indeed a blocking:

SPID: 67

Status: SUSPENDED

Login: liran

HostName: Liran-Computer

DBName: STG

Command: select * from [dbo].[STG_FACT_A]

Wait type: LCK_M_IS

BlkBy: 64

SPID: 64

Status: Sleeping

Login: LinkAdmin

HostName: DW

DBName: master

Command: sys.sp_getschemalock;1

So I understand process 64 is locking the schema preventing process 67 from running.

Why is it happening? why only when using dataflow?

This behavior only happens when the container is set to "Required" in the transactionOption, and stuck on the dataflow (yellow).

Thanks,

Liran

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.
Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

Execute SSIS package via sp

Is there a way to run an SSIS package via a stored procedure? I haven't foun
d
anything in BOL. I am currently doing this by running a job that executes th
e
package, but was wondering if there's a new sp for this.
Thanks, and happy new year,
TomTHi Tom,
Happy New Year!
From your descriptions, I understood you would like to know whether it is
possible to execute the SSIS via stored procedure. If I have misunderstood
your concern, please feel free to point it out.
Based on my knowledge, SQL Server 2005 introduced a new command prompt
utility dtexec. Using dtexec, you can run packages that are stored either
in the file system, in an instance of SQL Server 2005, or in the
Integration Services Package Store.
For more information, please refer the links below
How to: Run a Package Using the DTExec Utility
http://msdn2.microsoft.com/en-us/library/ms138023.aspx
Command Prompt Utilities (SSIS)
http://msdn2.microsoft.com/en-us/library/ms140258.aspx
Running Packages
http://msdn2.microsoft.com/en-us/library/ms141708.aspx
Alternatively, SQL Agent has a new feature called subsystems that makes it
possible to directly execute packages.
SQL Server Agent Subsystems
http://msdn2.microsoft.com/en-us/library/ms187100(en-US,SQL.90).aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.|||Thanks Michael, I think I'll stick with creating a job, and running that fro
m
an sp.
"Michael Cheng [MSFT]" wrote:

> Hi Tom,
> Happy New Year!
> From your descriptions, I understood you would like to know whether it is
> possible to execute the SSIS via stored procedure. If I have misunderstood
> your concern, please feel free to point it out.
> Based on my knowledge, SQL Server 2005 introduced a new command prompt
> utility dtexec. Using dtexec, you can run packages that are stored either
> in the file system, in an instance of SQL Server 2005, or in the
> Integration Services Package Store.
> For more information, please refer the links below
> How to: Run a Package Using the DTExec Utility
> http://msdn2.microsoft.com/en-us/library/ms138023.aspx
> Command Prompt Utilities (SSIS)
> http://msdn2.microsoft.com/en-us/library/ms140258.aspx
> Running Packages
> http://msdn2.microsoft.com/en-us/library/ms141708.aspx
> Alternatively, SQL Agent has a new feature called subsystems that makes it
> possible to directly execute packages.
> SQL Server Agent Subsystems
> http://msdn2.microsoft.com/en-us/library/ms187100(en-US,SQL.90).aspx
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> 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
.
>|||Hi Tom,
You are welcome, as always:)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.

Execute SQL Task: Error

I am running a Execute SQL Task which runs a script on a table. It gives me following error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Thanks,

Your query in the execute sql task is returning more than one row. Pretty much just what the error states.

If you don't need to return the results of the query to a variable, select "none" instead of "single row" in the ResultSet box. If you do in fact need to return more than one row into a variable, you'll need to select "Full Result Set." The variable you populate with single row needs to match (or be able to cast) the data type of the returned value. If you are using "Full Result Set" you'll need to use a variable of "object" data type and then enumerate through that variable using a foreach loop.|||http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Execute SQL task with update statement

I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?

Here is my query I'm running.

UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter

What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?

Please provide any error messages. That usually helps us help you.|||Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "Login timeout expired".

Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

[Execute SQL Task] Error: Executing the query "UPDATE encounter SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN encounter ON encounter.encounter_number = r.Encounter " failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is what the SSIS package says. I'm not sure how helpful it is but I can run the update statement in management studio all day long and it works great. It just bombs through the SSIS package.

|||

Seems as though the package is having problems talking to the server, "login timeout expired"

|||

I have all the timeouts settings set to 60 seconds, which should be plenty of time, one would think. When it does work it runs real fast-less than 4 seconds.

The main frustration is that it will work 1 time when I do something to the query but then it doesn't work again.

Execute SQL Task not running?

Hi All,

I have a strange problem that I can't find any information about and was hoping someone could help me out.

Some background:

* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.

* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.

* I then move the data from the staging table to a final table.

Here's the problem:

When the SQL Task like this:
WHILE @.@.rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0

is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.

Does anyone have any advice on the possible causes or where to investigate further?

Thanks,
s.

Turn on logging so you can find out what's going on.

-Jamie

|||Hi Jamie,

The log doesn't show anything different between a correct and incorrect run. I think I may have figured out an answer though. I finally figured out that the problem only occurs when I run multiple packages on multiple threads at the same time. Although none of the packages update the same table, perhaps it is something with the @.rowcount that is causing trouble? I was able to fix the problem (as far as I have seen so far) by changing the package executions to use dtexec.

s.

Execute SQL Task - SSIS

A stored function is created in MySQL Server running on Linux with following info:

mysql > delimiter $$

mysql > CREATE FUNCTION GetMaxKeyForSampleTable()

-> RETURNS DOUBLE READS SQL DATA

-> BEGIN

-> RETURN (SELECT MAX(MJD) FROM tblSampleTable);

-> END;

Query OK, 0 rows affected (0.02 sec)

mysql > delimiter ;

***The above function is successfully stored in the database.

"Execute SQL Task" add-in module has following info:

Name: Execute SQL Task

Description: Execute Task

TimeOut: 0

CodePage: 1252

ResultSet: Single row

Connection Type: ADO.NET

Connection: .MySQLServerLinux

SQLSourceType: Direct Input

SQL Statement: SELECT GetMaxKeyForSampleTable() AS MaxKey

IsQueryStoredProcedure: True

ByPassPrepare: False

Result Set:

Result Name Variable Name

MaxKey User:_dMaxKey

The following steps are taken:

1. Clicked "Parse Query" push button which results a pop-up dialog box -

"The query parsed correctly".

2. Clicked "OK" button.

3. Right clicked "Execute SQL Task" add-in module.

4. Selected "Execute Task".

"Execute SQL Task" changed its color from white to yellow to red

which means excution failed.

Please help me to figure out what should be done to make the excution successful.

Thank you very much in advance.

Paul Cho

First try the Progress tab, after it fails for some more details on the error|||

Do you mean break points by saying Progress Tab?

I set break points for "Execute SQL Task" add-in module.

However, the excution does not stop at break points.

Would you please help me on this issue?

If you meant something different, please elaborate on Progress Tab please.

Thank you very much in advance.

Paul Cho

|||

You have several tabs across the top of the SSIS designer, labeled Control Flow, Data Flow, etc. Progress is one of those. Error messages are reported on this tab.

Execute sp_start job on many clients

I have about 200 msde instances running on several machines.I want to
execute sp_start_job on several clients and manage the jobs such that
the job schedules specified by the clients do not clash and can be
changed remotely if requred by us without dropping and recreating the
jobs.
All this nees to be done form a central server.How can this be done ?Do
I have to create a web service to do this?
I would not want to register each client as a remote server?
Thanks in anticipations.
AjayHi
This sounds like you want to take the scheduling away from SQL Server Agent,
in which case you will just need to create the job but not schedule it, then
have your own scheduling starting off the job. It would not need to be a web
service unless that is how you implement the registration process.
John
"AG" <ajayz90@.hotmail.com> wrote in message
news:1125849309.901461.240560@.g43g2000cwa.googlegroups.com...
>I have about 200 msde instances running on several machines.I want to
> execute sp_start_job on several clients and manage the jobs such that
> the job schedules specified by the clients do not clash and can be
> changed remotely if requred by us without dropping and recreating the
> jobs.
> All this nees to be done form a central server.How can this be done ?Do
> I have to create a web service to do this?
> I would not want to register each client as a remote server?
> Thanks in anticipations.
>
> Ajay
>|||Here is what the scenario is like.
I have a sql server behind a fire wall so only the client msde instances
can acess the sql server , the connection cannot be initiated by the sql
server.I have 200 client MSDE instances and they have dts packages to
load the data.I want to be able to monitor their hear beat and also
centrally control their schedules.I was wondering what approch would be
good besides the web service sinc i caanor register the remote servers
on MY sql becuase of the fire wall.
AJay
Ajay Garg
Data Warehouse Programmer
MCDBA
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
If you are going to administer the jobs centrally then you will need some
way of starting the job, whether that is an agent application or web service
running on the MSDE client is in the design. You will probably need two way
communication to make it work.
An alternative would be to use a VPN which would allow you to register the
instances.
John
"Ajay Garg" <ajayz90@.hotmail.com> wrote in message
news:%236l384tsFHA.304@.TK2MSFTNGP11.phx.gbl...
> Here is what the scenario is like.
> I have a sql server behind a fire wall so only the client msde instances
> can acess the sql server , the connection cannot be initiated by the sql
> server.I have 200 client MSDE instances and they have dts packages to
> load the data.I want to be able to monitor their hear beat and also
> centrally control their schedules.I was wondering what approch would be
> good besides the web service sinc i caanor register the remote servers
> on MY sql becuase of the fire wall.
>
> AJay
>
> Ajay Garg
> Data Warehouse Programmer
> MCDBA
> *** Sent via Developersdex http://www.examnotes.net ***

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie

Execute Permission denied when running from IIS

My development environment is IIS 5.1, asp.net 2.0, Visual Web Developer 05 Express, MS Sql 2005 Express with XP Pro. I used a "stored procedure" in a webpage Formview to insert a record in a child table after inserting a record in the parent table. All went well when testing in VWD.

After deploying to remote site on same machine, I get an error

"EXECUTE permission denied on object 'usp_Insertdataset', database 'Job_Tracker_SQL', schema 'dbo'"

when trying to insert. I know that SQL Express is not suppose to support stored procedures. Is there a work around? I need to host this site on this machine for the immediate future.

Thanks

cbrcdr

I think that you are incorrect in stating that SQL Server Express does not support stored procedures. I'd be curious to see where that is documented (but, I have been proven wrong in the past!).

But, in any case, I would bet that in your development environment, you were connecting to the database as a DBO (database owner), and the same was not true for the remote site. By default, no one but a DBO is granted any permissions on database objects.

You should be able to grant execute permissions to the "Public" role, which should take care of allowing anyone who is permitted to connect to the database to execute your stored procedure. As an alternative, you can grant execute permission to invididual users too.

Execute the following SQL while connected to your database as the DBO user (i.e., from a query window or whatever mechanism you have available):

GRANT EXECUTE ON usp_InsertdatasetTO Public

|||

Jason

I saw a Feature Matrix on a Mircosoft Webpage that compared all the SQL products and it indicted that SQL Express did not support Stored Procedures. Maybe it was not up to date. I assumed it was right but you are. I used SSMSE and set the permissions on the Stored Procedure to execute for the login and it works.

Thanks, that was my last big hurdle for this phase of my application.

George