January 16, 2009 at 4:16 am
I'm trying to use the stored procedure below in SQL Server 2000, but I get the following error;
Server: Msg 170, Level 15, State 1, Line 4
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'much'.
Failed to get the call stack!
I know there difficulties passing tablenames but this is clearly not a tablename.
Var values used:
@BatchID = 461
@ErrorReason = 'DataElementID too much characters (>3)'
@WhereStatement = 'LEN(DataElementID) > 3'
CREATE PROCEDURE dbo.spWriteErrorRows
@BatchID INT,
@ErrorReason VARCHAR(200),
@WhereStatement VARCHAR(200)
AS
EXEC('
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ' + @ErrorReason + '
FROM dbo.ImportAccess
WHERE' + @WhereStatement)
GO
Obviously it can't handle the @ErrorReason variable. Is there any way to fix this?
January 16, 2009 at 4:35 am
henkvisser (1/16/2009)
I'm trying to use the stored procedure below in SQL Server 2000, but I get the following error;
Server: Msg 170, Level 15, State 1, Line 4
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'much'.
Failed to get the call stack!
I know there difficulties passing tablenames but this is clearly not a tablename.
Var values used:
@BatchID = 461
@ErrorReason = 'DataElementID too much characters (>3)'
@WhereStatement = 'LEN(DataElementID) > 3'
CREATE PROCEDURE dbo.spWriteErrorRows
@BatchID INT,
@ErrorReason VARCHAR(200),
@WhereStatement VARCHAR(200)
AS
EXEC('
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ' + @ErrorReason + '
FROM dbo.ImportAccess
WHERE' + @WhereStatement)
GO
Obviously it can't handle the @ErrorReason variable. Is there any way to fix this?
This way the code expects @ErrorReason to be a column name, to prevent this, replace ' + @ErrorReason + ' with ''' + @ErrorReason + '''
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 16, 2009 at 4:43 am
r.hensbergen (1/16/2009)
This way the code expects @ErrorReason to be a column name, to prevent this, replace
' + @ErrorReason + 'with''' + @ErrorReason + '''
Thanks Ronald, I replaced the variable the way you mentioned by adding two single quotes on both sides like this;
CREATE PROCEDURE dbo.spWriteErrorRows
@BatchID INT,
@ErrorReason VARCHAR(200),
@WhereStatement VARCHAR(200)
AS
EXEC('
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ''' + @ErrorReason + '''
FROM dbo.ImportAccess
WHERE' + @WhereStatement)
GO
But now I get the OPTIMIZER LOCK HINTS error:
Server: Msg 155, Level 15, State 1, Line 4
[Microsoft][ODBC SQL Server Driver][SQL Server]'DataElementID' is not a recognized OPTIMIZER LOCK HINTS option.
Failed to get the call stack!
January 16, 2009 at 4:44 am
Try additional quotes around the variables - two single quote for escape
EXEC('
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''
FROM dbo.ImportAccess
WHERE' + @WhereStatement)
January 16, 2009 at 4:46 am
Thanks but I just tried the same solution by Ronald but get the OPTIMIZER LOCK HINTS error.
January 16, 2009 at 4:58 am
put the whole query to a variable and print it. I think you need to give a space after the where clause
January 16, 2009 at 5:05 am
I agree that putting the SQL statement in a variable could have solved the problem, but I think I got it anyway. If you add an extra ') to the code, I think it should run then.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 16, 2009 at 5:05 am
set @sql='INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''
FROM dbo.ImportAccess
WHERE ' + @WhereStatement
print @sql
January 16, 2009 at 5:19 am
So I think the @WhereStatement wasn't actually part of the INSERT. If you get used to put the SQL in a variable, understanding of using the quotes is much easier. Below my solution based on the above post. set @sql='INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT
DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''
FROM dbo.ImportAccess
WHERE ''' + @WhereStatement + ''')
print @sql
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 16, 2009 at 7:50 am
Guys, I tried both;
First results of print @sql;
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), '463', 'DataElementID too much characters (>3)'
FROM dbo.ImportAccess
WHERE LEN(DataElementID) > 3
Looks promising, but the procedure generates this error;
Server: Msg 170, Level 15, State 1, Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'LEN(DataElementID) > 3'.
Failed to get the call stack!
@RETURN_VALUE = N/A
The second code by Ronald gives the following for @sql;
INSERT INTO dbo.ImportError
(DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)
SELECT DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), '463', 'DataElementID too much characters (>3)'
FROM dbo.ImportAccess
WHERE 'LEN(DataElementID) > 3'
Last line won't work, but after removing 4 quotes from the @WhereStatement variable it produces the same error as the first piece of codes does. (incorrect syntax)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply