query completed with error

Question

query completed with error

spectra

SSCarpal Tunnel

Points: 4120
More actions
January 2, 2017 at 3:55 am

#339090

when I run the folling script
I get error in SQL 2008 Message tab : Query completed with error
DECLARE @TableName as sysname
DECLARE @ColName as sysname
DECLARE @sql as nvarchar(4000)
DECLARE PK_Max_cursor CURSOR FOR
SELECT OBJECT_NAME(ic.OBJECT_ID) TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) PrimaryKey
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
OPEN PK_Max_cursor
FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'SELECT ''' + @TableName + ''' TableName, ''' +
@ColName + ''' PrimaryKey, MAX(' + @ColName +
') MaxValue FROM ' + @TableName
EXEC sp_executesql @sql
FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName
END
CLOSE PK_Max_cursor
DEALLOCATE PK_Max_cursor
Is there any issue in this script ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

The script doesn't have errors as such but it would possibly error on either the table name or missing schema prefix, suggest you add the table schema and use the quotename function for the table name.

😎

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 2

BEGIN TRY

EXEC sp_executesql @sql

END TRY

BEGIN CATCH

PRINT @sql -- I think you can see invalid statement....

END CATCH

sgmunson SSC Guru Points: 110640 More actions · Answer 3

spectra (1/2/2017)
when I run the folling script
I get error in SQL 2008 Message tab : Query completed with error
DECLARE @TableName as sysname
DECLARE @ColName as sysname
DECLARE @sql as nvarchar(4000)
DECLARE PK_Max_cursor CURSOR FOR
SELECT OBJECT_NAME(ic.OBJECT_ID) TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) PrimaryKey
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
OPEN PK_Max_cursor
FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'SELECT ''' + @TableName + ''' TableName, ''' +
@ColName + ''' PrimaryKey, MAX(' + @ColName +
') MaxValue FROM ' + @TableName
EXEC sp_executesql @sql
FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName
END
CLOSE PK_Max_cursor
DEALLOCATE PK_Max_cursor
Is there any issue in this script ?

My guess is that either a table name or a column name has a space or special character in it. Try this instead, which uses braces ([]) on both sides of the column name and table name portions of the query:

DECLARE @TableName as sysname;

DECLARE @ColName as sysname;

DECLARE @sql as nvarchar(4000);

DECLARE PK_Max_cursor CURSOR FOR

SELECT OBJECT_NAME(ic.[object_id]) TableName, COL_NAME(ic.[object_id], ic.column_id) PrimaryKey

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

WHERE i.is_primary_key = 1;

OPEN PK_Max_cursor;

FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = N'SELECT ''' + @TableName + ''' TableName, ''' +

@ColName + ''' PrimaryKey, MAX([' + @ColName +

']) MaxValue FROM [' + @TableName + N']';

EXEC sp_executesql @sql;

FETCH NEXT FROM PK_Max_cursor INTO @TableName, @ColName;

END

CLOSE PK_Max_cursor;

DEALLOCATE PK_Max_cursor;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQLisAwesome SSC-Addicted Points: 428 More actions · Answer 4

I used the CAST to convert any Text or VARCHAR(MAX) data types, seems to work without errors for me.

DECLARE @TableName AS sysname;

DECLARE @ColName AS sysname;

DECLARE @sql AS NVARCHAR(4000);

DECLARE PK_Max_cursor CURSOR

FOR

SELECT OBJECT_NAME(ic.object_id) TableName,

COL_NAME(ic.object_id, ic.column_id) PrimaryKey

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

WHERE i.is_primary_key = 1;

OPEN PK_Max_cursor;

FETCH NEXT FROM PK_Max_cursor

INTO @TableName,

@ColName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql

= N'SELECT ''' + @TableName + ''' TableName, ''' + @ColName + ''' PrimaryKey, MAX(CAST(' + @ColName

+ ' AS Varchar (50))), MAX(CAST([' + @ColName + '] as VARCHAR)) MaxValue FROM ' + @TableName;

PRINT @sql;