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


ADVERTISEMENT

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

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 Statement

Aug 1, 2007

Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:

EXEC(SELECT QRY FROM temp_Update)

Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:

Msg 156, Level 15, State 1, Line 123Incorrect syntax near the keyword 'SELECT'.Msg 102, Level 15, State 1, Line 123Incorrect syntax near ')'.

Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.

View 9 Replies View Related

EXEC Statement

Jun 20, 2006

Hi everyone,
What is EXEC statement ?? What is the usage and purpose of it ??
It is really difficult to find ant resource about this keywords that's why I would like you to help me.

Thanks

View 1 Replies View Related

Error Near Exec Statement

Sep 19, 2005

Hi Guys, I have been trying to get this SQL string below to run, but for some reason I carry on getting an error near the EXEC Statements.

Does anyone have any idea what I doing wrong?

CREATE PROCEDURE [dbo].[UpdateWarnings]
@EndDate DateTime
AS
DECLARE @iid varchar(100)
DECLARE @fid varchar(100)

SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')

IF @iid <> ''
BEGIN
EXEC('UPDATE memorial SET active=0 WHERE id IN ('+ @iid +')')
END

SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')

IF @fid <> ''
BEGIN
EXEC('UPDATE memorial SET warning=(warning+1) WHERE id IN ('+ @fid + ')')
END

IF @enddate <> ''
BEGIN
UPDATE warnings SET startdate=getdate(), enddate=@enddate, warnings=(warnings + 1) WHERE id=1
END
GO

View 1 Replies View Related

Exec In Select Statement

Jan 2, 2007

hi,

can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..

Select column1, coulumn2, (select EXEC SP_NAme), blabla

thanks in advance... :)

View 6 Replies View Related

Stored Procedure Exec An SQL Statement

Oct 2, 2007

I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?

View 7 Replies View Related

SProc - Ad Hoc Sql Statement With COUNT For Exec(@SQL) ??

May 14, 2001

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

View 4 Replies View Related

Using Single Quotes In EXEC Statement

Mar 27, 2001

View 1 Replies View Related

An INSERT EXEC Statement Cannot Be Nested.

Sep 19, 2007

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@doctype nvarchar(2),
@datefrom datetime,
@dateto datetime,
@includegrn char(1),
@includesa char(1),
@includedata nvarchar(5)
AS
BEGIN
declare @dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@DOCTYPE = 'SI')
BEGIN
Insert Into @dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
ELSE
BEGIN
Insert Into @dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
Select doccode,docdate,debtoraccount From @dtReturnData
END

So how can I select a nested store procedure? can someone help me

View 1 Replies View Related

Openrowset With Parameter In Exec Statement?

Sep 5, 2007

Hi!
Is it possible to call proc with parameter in openrowset?:
declare @str varchar(100);

select @str = 'EXEC SP_HELPTEXT ''createReport''';

select * from

OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str)
AS a;

Incorrect syntax near '@str'.

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

select * from

OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC
SP_HELPTEXT ''createReport''') AS a;

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE
DB provider "SQLNCLI" for linked server "(null)" indicates that either
the object has no columns or the current user does not have
permissions on that object.

Best regards
B. D. Jensen

View 6 Replies View Related

INSERT EXEC Statement Cannot Be Nested

Apr 12, 2006

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error



An INSERT EXEC statement cannot be nested.



heres the actual insert code:



set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'

INSERT INTO reportData

exec (@sqlString)



ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID...........



that still doesnt work. same error. how can this be sorted

View 13 Replies View Related

Nested Insert Exec Statement

Feb 7, 2008

I am using the following statement in a SP.

EXECUTE (' INSERT INTO #OutPut

EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units

)

AND IN h_DailyDividend I am using the following statement.

EXECUTE (' INSERT INTO #TickerTable

EXEC h_SecMstr_SecMap_TQAExch_Info

@IDList = '+@TickerTable+',

@IsTable = 1,

@Type = 0,

@OutPutFormat = 0,

@VenType = 14 ')

And i am getting the following error.

Message: An INSERT EXEC statement cannot be nested.

Can any body help me out how to solve this problem.

Regards
Sulaman

View 4 Replies View Related

ERROR:- An INSERT EXEC Statement Cannot Be Nested.

May 3, 2004

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END


CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .........;
ST2..........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW.......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .........;
ST2..........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKS

View 13 Replies View Related

An INSERT EXEC Statement Cannot Be Nested Error.

Nov 28, 2005

Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks

View 2 Replies View Related

Trigger With Exec Statement Blocking Execution Of Triggering Cmd?

Jul 20, 2005

I have a trigger on a table. I am trying to dynamically log thechanged fields on the table to another table, so I am iteratingthrough the bits in COLUMNS_UPDATED() to find what's changed, andgetting the column name programatically. This is all working fine.If I do a regular insert command in my trigger then everything worksfine. However, since I want to retrieve data from the column namewhich I got programatically from the inserted and deleted tables (toget the old and new values) I wanted to do something like this:insert into auditTransactionLog (TableName,PrimaryKeyId,ColumnName,OldValue, NewValue, ActionType) EXEC( 'SELECT(''cmContactInfo''), I.contactID,'''+ @colname+''', D.'+@colname+',I.'+@colname+', '+@action+' FROM inserted I INNER JOIN Deleted D onI.ContactId = D.ContactId')The presence of this line of code appears to be preventing theupdating of the table with the trigger. Is there some reason why Ican't do the EXEC in the trigger? If I did it without EXEC it worksfine but I have no idea of getting at the D. and I. @colname columnsotherwise.Thanks for any help!Rebecca

View 1 Replies View Related

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

Feb 8, 2008

Hi

I've used a temporary table in the stored procedure

I've created it as DECLARE @Temp TABLE (id INT)

in select clause I've used this temp table through the joins..

But I've also used a varchar variable to store my criteria...

which I'm building in the stored procedure

so inorder to use it in the where clause I used

EXEC ('SELECT ....................


FROM @Temp T

LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )


Unfortunately this is not working.
Giving the errror


Must declare the variable '@TempT'.

I had to use Temporary table using #, which I feel is really waste of memory...

Is there a way by which I can use my Table variable in the EXEC statement.

Thanks In advance

View 4 Replies View Related

Error While Executing A A Query String Using EXEC Statement

Sep 18, 2007

Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a



To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet

@Category - Will be passed as a parameter to the stored proc


DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @vsBulkSQL - This prints the folliwing statement


INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a


Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.






Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

View 4 Replies View Related

How Do I Use A @variable To Hold On Value Return From An Exec (&#39;string Command&#39;) Statement.

Sep 19, 2000

How do I use a @variable to hold on value return from an exec ('string command') statement.

Example for:

declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)

set @db = 'DBNAME'
set @OldOwner = 'USERNAME'

select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)

How can I use @OldID to hold on UID return from this statement.

When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.

Thanks.

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

Following is the stored procedure iam trying to create.Here i am trying to

First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.

This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 Replies View Related

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 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







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