CODEWITHSUNDEEP
sql-servert-sqlssis
Daniel Sh.
Daniel Sh.

Reputation: 2074

Weird issue with create table

I'm working on a SSIS package. I have an array with a list of names. Those names will be table names.

To create those tables, I loop a SQL task editor EXECUTING a stored procedure accepting a parameter (name of the table)

Code for the stored procedure:

ALTER PROCEDURE [dbo].[TLP.CreaTable]
(
    @TableName  VARCHAR(255)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(4000)
    SET @SQL = 'IF NOT EXISTS ( SELECT  * 
                                FROM    sysobjects 
                                WHERE   id = OBJECT_ID(N''[dbo].' + @TableName + ''') 
                                AND     xtype in (N''U'')) 
                    CREATE TABLE [dbo].' + @TableName + '(
                        [Serial] [int] NOT NULL,
                        [Marc] [bit] NOT NULL,
                        [Sell] [bit] NOT NULL
) ON [PRIMARY]'

    EXEC (@SQL)
END

The issue comes when one of the names in the array is a number. I must say all the names enter the stored procedure with []. I've created a breakpoint before each execution and when the table name is a number it brings an error inside the SSIS (Also for you to have a better idea Ive ran the stored procedure from the SQL Server Management Studio, and the error persists, Ive used the table name [3001] which is actually a real name that's causing the problem).

EXEC dbo.[TLP.CreaTable] [3001]

Msg 170, Level 15, State 1, Line 5 Line 5: Incorrect syntax near '.3001'.

If I do something like 

EXEC dbo.[TLP.CreaTable] RANDOM_NAME

It works just perfectly

Apparently there's some '.' in the middle. But I just cant find it in the code.

Thanks

EDIT:

It was a matter of quotation. It worked in the SQL Server Management Studio. But when I tried it in the SSIS the error persisted. Tried modifying the C# code to add '[ ]' instead of just [] and it didn't work eiter.

The error inside the SSIS is as follows.

Error: 0xC002F210 at Create Table 030698, Execute SQL Task: Executing the query "EXEC dbo.[TLP.CreaTable] ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

With breakpoints I watched the variable for the table name and it was ok '[3001]'

The calling inside the SQL Task Editor is 

EXEC dbo.[TLP.CreaTable] ?

But somehow the EXEC dbo.[TLP.CreaTable] '[3001]' works in SQL Server Management Studio, but doesn't inside the SSIS.

Any idea? Thanks.

EDIT 2

Since Im pretty new to the forum, should I ask this second question in a new 'question'? Since the matter issued in the subject was solve. Thanks a lot.

EDIT 3

Fixed. Parameter in CREATE TABLE SQL task editor was long, changed it to varchar and it went smooth. Thanks

Upvotes: 2

Views: 1482

Answers (4)

Darren
Darren

Reputation: 70776

You can't create a table name with a number directly. Try putting the table name in quotations: I.e. 

EXEC [dbo].[TLP.CreaTable] ['100']

Upvotes: 1

Arion
Arion

Reputation: 31249

Add brackets around the

[' + @TableName + ']

So that the store procedure looks like this:

ALTER PROCEDURE [dbo].[TLP.CreaTable]
(
    @TableName  VARCHAR(255)
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(4000)
    SET @SQL = 'IF NOT EXISTS ( SELECT  * 
                                FROM    sysobjects 
                                WHERE   id = OBJECT_ID(N''[dbo].' + @TableName + ''') 
                                AND     xtype in (N''U'')) 
                    CREATE TABLE [dbo].[' + @TableName + '](
                        [Serial] [int] NOT NULL,
                        [Marc] [bit] NOT NULL,
                        [Sell] [bit] NOT NULL
) ON [PRIMARY]'

    EXEC (@SQL)
END

Upvotes: 2

Bridge
Bridge

Reputation: 30711

You could try wrapping @TableName with the QuoteName function which should escape the table name for you. Change that line to:

CREATE TABLE [dbo].' + QuoteName(@TableName) + '(

Edit: Might also be an idea to wrap the table name in square brackets, in case it's a reserved word:

CREATE TABLE [dbo].[' + QuoteName(@TableName) + '] (

Upvotes: 3

juergen d
juergen d

Reputation: 204894

try using ' around the table name

EXEC dbo.[TLP.CreaTable] '[3001]'

Upvotes: 2

Related Questions