Can I Have Exec(...) Statements Within A Transcation ?

Sep 17, 2004

Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(....) statements are there in the transaction ? Each stored procedure is made up of an action query.





begin tran


exec("storeprocedure1('2')")


if @@error=0


begin


exec("storeprocedure2")


if @@error=0


begin


exec("storeprocedure3(122)")


if @error=0


commit tran


else


rollback tran


end


else


rollback tran


end


else


rollback tran

View 1 Replies


ADVERTISEMENT

SELECT EXEC Statements In SQL

Jun 6, 2008

I  am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
 
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
 FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE  M.EMAIL = @UserEmail
 

View 5 Replies View Related

How To Do The Database Transcation Replica ?

Feb 5, 2008

Hi,

I am new to SQL server.

I have two databases lets call them A & B, they reside in two PCs, each pC has its own C/C++ program
working with A or B, Two PCs are linked by PPP (for example), but the link is not permonent available,
the link is only available at certain time period, for example, at midnight.and the link is only last
certain period, during this period, A & B will do the transcation replica( just heard about that!) to
make them identical, A=B.

Now are my questions.

(1) Can database use dial up modem itself ? For PC program it is not the problem, but I am not sure whether
MS SQL server database can be configured to do such job automatically ?

(2) Can database have awareness of whether the link is available or not ? For example A is dialing modem, and
B realize that there is a incoming call.

(3) Once link is established, how two databases start to do the transcation replica ? Does SQL server has such ability to do this automatically ?

(4) Can database notice PC program that changes has occured ? so PC program can process it, see display new data.

Thanks

nick

View 1 Replies View Related

Inser A Table From A View In A Transcation

Nov 30, 2005

Hello, everyone:

I have to post this problem again because I didn't get the correct way.

I have SQL Server 2000 on client (Windows XP) and server (Windows 2003). DTC is running on both side. There is a transcation like,

BEGIN TRAN
INSERT MyTable SELECT * FROM MyView
COMMIT

MyView get data by:

SELECT * FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=PROD;UserID=DBDev;Password=WORLDWID').PRODD ata.dbo.CService

PROD is the production server.

When I run this query, there is the error message,

Server: Msg 8501, Level 16, State 1, Line 2
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

Any idea is appreciated.

Thanks

ZYT

View 2 Replies View Related

Visibility Into Active Transcation Using LINQ To SQL And SQL Server Compact 3.5

Jan 6, 2008



I am talking to SQL Server CE through LINQ to SQL in Visual Studio 2008. I have a table that uses location information as the primary key. The state associated with the location may change so sometimes I add new records and sometimes I update existing records. Before adding records to the table I verify that no existing record exists with the same primary key.

When I query against SQL Server Compact 3.5, I don't pick up records in the active transaction. I thought that the previous version of SQL Server CE did pick up records in the active transaction.

View 3 Replies View Related

Can Exec Select But Can't Exec Sp

Oct 31, 2007

I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).

I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.

These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.

Can some one explain why?

View 5 Replies View Related

Using Exec

Feb 29, 2000

When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob

View 1 Replies View Related

Exec

Jul 22, 2003

When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?

Thanks

View 4 Replies View Related

Exec In My Sp

Nov 1, 2007

I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...

create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)

---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------

as

declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!

------------------------Set Statements using @TableName Var------------------------------------------

Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName

------------------------------------------------------------------------------------------------------

Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)

Select(@Filename),(exec(DSNo)), ................

Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...

View 2 Replies View Related

Exec Sql

Apr 28, 2006

I need help understanding the syntax of the "exec sql" statement.

i am looking at code that build an sql string such as

sql="exec SOMETHING Session("id")"

or something like that.

then, there is

conn.execute(sql)

My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.

i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.

in particular, the code i am examining is

sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)

i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?

View 2 Replies View Related

MDX And EXEC

Feb 17, 2006

Hi,

Can I execute my MDX statement as in the exhibit format!



DECLARE @test VARCHAR(MAX)

SET @test = 'WITH test AS (SELECT * FROM merchants)'

EXEC(@test)

SELECT * FROM test



If I don't use that dynamice sql statement, everything work fine.



Thanks,

Myo

View 1 Replies View Related

EXEC

Mar 28, 2006

Hi,

I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...

declare @strsql varchar(500)


set @strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'

exec (@strsql)

When I execute the above script, I immeaditely see the results in the query analyser like below:

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0

Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....

Just wondering whats goin on..

Thanks.

Ranga



View 3 Replies View Related

Get A Return Value With EXEC?

Aug 29, 2007

Hi, I have an sql query like this :DECLARE         @TableName varchar(200),    @ColumnName varchar(200),    @EmployeeID varchar(200),    @Result    varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' +  @ColumnName + ' from ' +  @TableName + ' where Recid = ' + @CustomerID + '');Print @Result;   I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks 

View 1 Replies View Related

Exec And Sprocs

Nov 7, 2000

is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?

i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.

View 1 Replies View Related

EXEC Dynamic Sql

Oct 17, 1999

How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)

View 2 Replies View Related

Problem With USE And EXEC

Aug 23, 2000

I found this statement in BOL and it didn't make it to work.Is anybody out there who ha the same problem?
Database is MASTER.
USE master EXEC ("USE pubs") SELECT * FROM authors

View 1 Replies View Related

EXEC Dynamic Sql

May 13, 2003

Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.

Print @Table (db and table name: opms..transactions)
Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
Print @sql
EXEC (@sql)

opms..Transactions
Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)

Server: Msg 1004, Level 15, State 1, Line 1
Invalid column prefix 'opms.': No table name specified

Any idea?

mipo

View 2 Replies View Related

EXEC Statement

Aug 22, 2002

I would like to execute something like that with sql6.5 :

select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (cmd)

I tried like this but I don't know what's wrong

exec ("USE "+ RTRIM(@dbname) +"exec sp_helprotect")

Thank you

View 1 Replies View Related

How To Use @@ERROR For EXEC(@SQL)

Jun 25, 2005

Hi,

I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that...
----------------------------------------------
DECLARE
,@DBName VARCHAR(128)
,@LoginName VARCHAR(128)
,@SQL VARCHAR(2000)

SET @DBName='dbname'
SET @LoginName='loginname'

SELECT @SQL=@DBName+'..SP_EXECUTESQL N''SP_REVOKEDBACCESS ['+@LoginName+']'''

EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
PRINT @@ERROR
END
--------------------------------------------------------------

Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like ,
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.

But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .

So How can I retrieve the error value 15008 in a variable ..?


:confused:

View 9 Replies View Related

Dynamic SQL And EXEC

Aug 11, 2005

Hello,

I'm trying to do something like the following, but it keeps complaining that I need to declare @max, even though I have (and it is of the same type as link_id).

EXEC('SELECT @max=MAX(link_id) FROM '+ @str1)

I've looked into sp_executesql but I'm not entirely sure how that functions. Any suggestions?

View 3 Replies View Related

Exec Xp_sendmail

Aug 12, 2005

How do I attached the file by using xp_sendmail?

exec xp_sendmail
@recipients='myemail@yahoo.com',
@subject='test',
@attach_results='True'
??? 'C:FileName.xls'

View 3 Replies View Related

How To Allow Use To Exec Xp_sendmail

Sep 20, 2004

I have a stored proc that assigns a value to a field based on user input from an Access front end.

The last part of the stored proc sends an email if certain conditions are met.

It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?

The users are getting this message:

EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)

View 1 Replies View Related

Exec In A Function

Oct 15, 2004

I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?

Functions can not call stored procedures, and they can not use temporary tables.

Thanks much

View 1 Replies View Related

Exec @string

Oct 24, 2005

I am trying to do an insert statement utilizing a variable string.

something like:

Set @cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @lServer + ';User ID=' + @user + ';Password=' + @pword + char(39) + '
).myServer..

Insert into #Temp_table (field1, field2)
select exec @cString

--What is the syntax for this?

View 3 Replies View Related

Return A Value Using EXEC

Jan 12, 2004

OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:

CREATE PROCEDURE dbo.ExecFunction ( @ScalarSELECTString varchar(250), @@ReturnVal sql_variant )

set @@ReturnVal = ( exec @ScalarSELECTString )

go


So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.

View 8 Replies View Related

Insert Into...exec-- Help

Jan 21, 2004

I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".

declare @thestringall varchar(1000)
set @thestringall = 'select statment here'

insert into #temptable exec (@thestringall)

Thanks
Wooanaz

View 2 Replies View Related

Exec Statement

Mar 23, 2004

hi guys

maybe an easy one for you

in stored procedure I create follving select

@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)

problem is thant I want to gave return value from this select

something like
set @return = execute(@cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?

View 1 Replies View Related

Exec Statement

Mar 23, 2004

hi guys

maybe an easy one for you

in stored procedure I create follving select

@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)

problem is thant I want to gave return value from this select

something like
set @return = execute(@cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?

View 3 Replies View Related

Exec Without Results

Feb 20, 2007

this might sound dumb or impossible, but is there a way to run exec commands forcing them to not return results

View 6 Replies View Related

INSERT-EXEC

Apr 19, 2007

I need get the o/p of a system sp into a table. I am doing the following,

insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'

Code is really not important. Any sys SP can replace the above code.

I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.

I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.

I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?

------------------------
I think, therefore I am - Rene Descartes

View 8 Replies View Related

Exec A Function

Oct 11, 2007

Hi,

How to exec the following function from my select statement and get my return value:-

ALTER FUNCTION [dbo].[fn_qty]
(@qty decimal (10,4),
@price decimal (10,4),
@pieces int,
@mpt int=0)

returns decimal (10,4)

AS
begin
declare @totamt decimal (10,4)
if @pieces = 0
begin
SET @totamt = (@QTY * @price)
end
else
begin
SELECT @mpt = (SELECT case mpq
when 1 then 10
when 2 then 100
else 100
end
FROM ims.parm)
SET @totamt=(((FLOOR(@QTY)*@PRICE))+(((@qty-FLOOR(@QTY))* @mpt)/@pieces)*@price)
end
return @totamt
end

I can exec the function as exec command as follows:-
exec @totamt( erpinv.dbo.fn_qty 3.5,6,24 )


Best Regards

View 14 Replies View Related

EXEC SQL Task

Nov 9, 2007

Hello, I am very, very new to the world of SSIS and from a totally different platform (iSeries), learning, digging and finding golden nuggets as I go.

Two questions please.

Question One:

I am presently encountering the following exception.

SSIS package "DW.CUST_CNCL_ORD.dtsx" starting.
Error: 0xC00291E2 at BP Parter Exec SQL, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Task failed: BP Parter Exec SQL
Warning: 0x80019002 at DW CUST_CNCL_ORD: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DW.CUST_CNCL_ORD.dtsx" finished: Failure.


Try as I do to find information regarding this exception, I am not able to. Can anyone share their wisdom on this topic?


Question two:

Is there a book that you may recommend that will contribute to my learning this new platform and evironment? I presently have the SQL Server 2005 integration services book.

Thank you in advance for any information you may provide.

God Bless.

Thanks,

Carlos.

View 5 Replies View Related

Exec() Statement

Dec 16, 2007

declare @rej_nm as varchar(50)
exec('select count(*), ' + "'@rej_nm'" + 'from ' + @rej_nm)

trying to return the rowcount AND the name of the object. return set should look like this:

12, tbl_name

i've tried single quotes around @rej_nm, nested single quotes, single/double nested, etc..

instead, i get an error stating that @rej_nm is not a valid column of tbl_name. bottomline...

how do i return a variable 'value' within a select statement...

thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved