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


ADVERTISEMENT

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

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

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

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

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

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

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

Exec Pkg Task: Error 0xC0202009 While Preparing To Load The Package. An OLE DB Error Has Occurred. Error Code: 0x%1!8.8X!.

Feb 21, 2007

I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says

Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.



Any clue ?


Thanks,
Fahad

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

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

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

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

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

EXEC And Error Handling

Sep 2, 2004

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @@ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @SQL:

EXEC(@SQL)

IF @@ERROR <> 0

BEGIN
GOTO ErrorHandler
END


Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @@ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@SQL)).

Any help would be appreciated.

Barry

View 1 Replies View Related

EXEC Sp_attach_db Have A Error

Feb 2, 2004

I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @dbname = N'DS2004',
@filename1 = N'd:ds2004_data.mdf',
@filename2 = N'd:ds2004_log.Ldf'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanks

View 2 Replies View Related

SQL Exec Error Messages

Mar 12, 2008

Hi

I am trying to populate an SQL DB via ODBC. If this connection fails ie SQL server crashes, the data is then written to a local ACCESS table.

To do this I need to look at the SQLExec result code.

what are the error codes for comunication failure and duplicate record? They seem to be the same '307'

The duplicates need to be logged to another table so they are not lost.

Heres the code


IF SQLDBConnectError = 0 THEN //Log to SQL

IF lhSQL1 <> -1 THEN // SQL Connection OK

lsSQL1 = "INSERT INTO " + sTable2Use + sPostFix + " (TagnameKey,TimeDate,Tagname,EngValue,ImportInfo,Comment,NZDSTOffset) VALUES ('" + lsKey +"','" + lsSQLDate + "','" + sTagname + "','" + lsEngValue + "','" + sInfo + "','" + sComment + "','" + sUTCOffset+"')"

lnResult1 = SQLExec(lhSQL1, lsSQL1);

lsSQLErr1 = SQLErrMsg();

IF (lnResult1 <> 0) AND (lnResult1 <> 307) THEN

lnReturn1 = 1;

SQLEnd(lhSQL1);

SQLConnectionActive=0; !Display Connection Status on Screen

WCSSQLDBConnectError = 1; !Force to Access logging

SQLDisconnect(lhSQL); !Close SQL DB Connection

END!If;

IF (lnResult1 =307) THEN

lnReturn1 = 1;

SQLEnd(lhSQL1)

// Insert into Duplicates table (no Primary Keys)

lsSQL1 = "INSERT INTO NSCC_CitectDup " + sPostFix + " (TagnameKey,TimeDate,Tagname,EngValue,ImportInfo,Comment,NZDSTOffset) VALUES ('" + lsKey +"','" + lsSQLDate + "','" + sTagname + "','" + lsEngValue + "','" + sInfo + "','" + sComment + "','" + sUTCOffset+"')"



//lsSQL1 = "INSERT INTO NSCC_CitectDup" + sTable2Use + sPostFix + " (TagnameKey,TimeDate,Tagname,EngValue,ImportInfo,Comment) VALUES ('" + lsKey +"','" + lsSQLDate + "','" + sTagname + "','" + lsEngValue + "','" + sInfo + "','" + sComment +"')"

lnResult1 = SQLExec(lhSQL1, lsSQL1);

lsSQLErr1 = SQLErrMsg();

SQLEnd(lhSQL1)

END!If;

SQLEnd(lhSQL1);

ELSE

SQLConnectionActive=0;

SQLDBConnectError = 1;

END



END

Regards

Eugene

View 1 Replies View Related

SQL Error When Using EXEC In Stored Proc

Jul 10, 2006

I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong.
CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL)
AS
EXEC ('SELECT  PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated
 FROM vw_GetPersons  WHERE FirstName LIKE ''%' + @FirstName + '%'' AND  FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName')
GO

View 12 Replies View Related

Sql Server Error EXEC -- OUTPUT

Sep 15, 2004

I have a c# app. This is a piece of code out of a stored proc. it is erroring: Procedure or function getTopParentDealerFromChildDealer has too many arguments
OR
@dealerID is not a parameter for procedure getTopParentDealerFromChildDealer.(if I put ",@dealerID=@parentID)

I have tried all combinations "@dealerID",@dealerID=@parentID" etc.

BEGIN
--get the top parent dealerID
DECLARE @parentID INT
SET @parentID = 0
EXEC getTopParentDealerFromChildDealer @dealerID, @parentID OUTPUT
IF (@parentID>0)
BEGIN

------------------------------------------------------
here is the getTopParentDealerFromChildDealer as called
------------------------------------------------------
ALTER PROCEDURE getTopParentDealerFromChildDealer @childDealerID INT
AS

SET NOCOUNT ON
DECLARE @dealerID INT
DECLARE @parentID INT
SET @dealerID = 0
SELECT @dealerID = dealerParentID from dealerRelations where dealerChildID = @childDealerID

WHILE @dealerID <> 0
BEGIN
declare @temp INT
set @temp = @dealerID
IF (SELECT count(dealerParentID) FROM dealerRelations WHERE dealerChildID = @temp)>=1
BEGIN
SELECT @dealerID = dealerParentID
FROM dealerRelations where dealerChildID = @temp
END
ELSE
BEGIN
SET @dealerID=0
set @parentID = @temp
END
END

if (@parentID IS NULL)
BEGIN
set @parentID = 0
--set @parentID = @dealerID
END

return @parentID

I don't usually use stored procedures but the job I have taken over previously used them. Any help would be much appreciated.

Thanks

View 3 Replies View Related

Internal Error For EXEC Xp_trace_setqueuedestination

Apr 5, 2004

I am trying to setup a trace and save result to remote server by using EXEC xp_trace_setqueuedestination command. Same script was running fine on two server and failed on the other two (see attached error message). Search the Web and Microsoft don't find any clue. Can any one out there know anything about it.

Thank in advance.

Austinh

Server: Msg 19098, Level 10, State 1, Line 0
Internal Error
QueueHandle
-----------
8

View 2 Replies View Related

Exec Xp_sendmail Error On SQL Server 2000

Apr 23, 2007

I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.

Here is the trigger creation script and error message

use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO

CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @email_subject varchar(100),
@email_content varchar(4000),
@email_recipients varchar(50)

set @email_subject='SQL Mail test mail'
set @email_recipients='some@world.com.tw'
set @email_content='this is a test mail, don't reply this mail'

exec master.dbo.xp_sendmail @recipients=@email_recipients,@subject=@email_subj ect,@message=@email_content
GO

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Appreciate any prompt reply.

JD

View 5 Replies View Related







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