how to correect error message

Question

how to correect error message

Michael Ashby 40

I am using the following to create a database to import data from a csv file.

DECLARE @sql NVARCHAR(MAX)

DECLARE @filePath NVARCHAR(MAX) = "C:\Users\aussi\OneDrive\Desktop! Complete Family - BMDs_people_EN.csv"

DECLARE @tableName NVARCHAR(MAX) = 'dbo.Personal_Details'

DECLARE @colString NVARCHAR(MAX)

SET @sql = 'SELECT @res = LEFT(BulkColumn, CHARINDEX(CHAR(10),BulkColumn)) FROM OPENROWSET(BULK ''' + @filePath + ''', SINGLE_CLOB) AS x'

exec sp_executesql @sql, N'@res NVARCHAR(MAX) output', @colString output;

SELECT @sql = 'DROP TABLE IF EXISTS ' + @tableName + '; CREATE TABLE [dbo].' + @tableName + ' '

FROM (

SELECT ' [' + value + '] nvarchar(max) ' as name

FROM STRING_SPLIT(@colString, ',')

) t

EXECUTE(@sql)

When I excute the code i get the following error message: Msg 137, Level 15, State 2, Line 15

Must declare the scalar variable "@sql".

Can anyone tell what I am doing wrong,

Many thanks

Erland Sommarskog 133.2K Reputation points MVP Volunteer Moderator

2026-03-04T18:46:16.2533333+00:00

When I run your code, I get the error:

Msg 207, Level 16, State 1, Line 3 Invalid column name 'C:\Users\aussi\OneDrive\Desktop! Complete Family - BMDs_people_EN.csv'.

This is because you have enclosed the file path in double quotes. When I correct that, I get the error that the file is not found.

This tells me that there are no undeclared variables, because such errors are detected in an earlier phase than missing column names or missing files.

Thus, the error you get about the variable @sql, must come from some other piece of code than the you have posted.

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 133.2K Reputation points MVP Volunteer Moderator

2026-03-04T18:46:16.2533333+00:00

When I run your code, I get the error:

Msg 207, Level 16, State 1, Line 3 Invalid column name 'C:\Users\aussi\OneDrive\Desktop! Complete Family - BMDs_people_EN.csv'.

This is because you have enclosed the file path in double quotes. When I correct that, I get the error that the file is not found.

This tells me that there are no undeclared variables, because such errors are detected in an earlier phase than missing column names or missing files.

Thus, the error you get about the variable @sql, must come from some other piece of code than the you have posted.

Answer 1

The error occurs because @sql goes out of scope or is referenced in a batch where it has not been declared. In the posted script there are also other issues that will prevent it from working as intended.

Key problems and fixes:

Use single quotes for string literals in T‑SQL

   DECLARE @filePath NVARCHAR(MAX) = 'C:\Users\aussi\OneDrive\Desktop\! Complete Family - BMDs_people_EN.csv';

Ensure everything is in a single batch and @sql is declared before every use

   DECLARE @sql       NVARCHAR(MAX);
   DECLARE @filePath  NVARCHAR(MAX) = 'C:\Users\aussi\OneDrive\Desktop\! Complete Family - BMDs_people_EN.csv';
   DECLARE @tableName NVARCHAR(256) = 'Personal_Details';
   DECLARE @colString NVARCHAR(MAX);
   
   -- Read header line from file
   SET @sql = N'SELECT @res = LEFT(BulkColumn, CHARINDEX(CHAR(10), BulkColumn))
                FROM OPENROWSET(BULK ''' + @filePath + ''', SINGLE_CLOB) AS x;';
   
   EXEC sp_executesql
       @sql,
       N'@res NVARCHAR(MAX) OUTPUT',
       @res = @colString OUTPUT;
   
   -- Build CREATE TABLE statement
   SELECT @sql = N'DROP TABLE IF EXISTS dbo.' + QUOTENAME(@tableName) + N';
                   CREATE TABLE dbo.' + QUOTENAME(@tableName) + N' (' +
                   STRING_AGG(N'[' + value + N'] NVARCHAR(MAX)', N',') + N');'
   FROM STRING_SPLIT(@colString, ',');
   
   EXEC(@sql);

Important points:

DECLARE @sql appears once at the top and is reused; there is no GO between declarations and uses.
@colString is passed as an OUTPUT parameter (@res = @colString OUTPUT).
@tableName is used without dbo. prefix duplication and is quoted with QUOTENAME to avoid invalid identifiers.

Avoid GO between declarations and uses If there is a GO between DECLARE @sql and EXECUTE(@sql), the variable is not visible in the later batch and SQL Server raises “Must declare the scalar variable @sql.” Remove any GO separators inside this script.

Once these changes are applied and the script is kept in a single batch, the @sql variable will be recognized and the error Msg 137 will be resolved.

References:

Share via

how to correect error message

0 additional answers

Your answer