June 21, 2010 at 9:59 pm
Hi All,
I created a variable with name Scenario and datatype int. Now I mapped this variable in the execute sql task and named it 0. In the SQL Statement in execute sql task I have this below query
Declare @Scenario INT
SET @Scenario = ?
insert into table (Scenario)
Select @Scenario as Scenario
This is giving me an error "failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Basically I want to insert the variable name into a table............
Please advice.
Thanks in advance
June 22, 2010 at 3:26 am
I believe you forgot the table name in your query.
Personally, I think it is easier to store the SQL statement in a variable and then configure this variable with an expression. In your case, the variable - let's say @SQL_Statement - would look like this:
"INSERT INTO TABLE myTable (Scenario)
SELECT " + @Scenario + " AS Scenario"
Then use this variable as the source for your SQL statement.
It could be necessary do the a cast on the variable @Scenario, that depends on how you declared that variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 23, 2010 at 9:01 am
I think the only problem is the sql syntax. I was able to recreate it fine.
insert into table (Scenario)
Select @Scenario as Scenario
INSERT INTO Scenario
SELECT @Scenario as Scenario
June 23, 2010 at 2:13 pm
DECLARE @a INT
SET @a = ?
insert into dbo.test_delete_yes
select @a as value
This is the query I have written in the sql_statement of the Execute SQL Task. And I declared a variable scenario as below.
variable
name scope datatype value
scenario---package---int32------0
In the parameter mapping tab my mapping is as below:
variableName direction data_type parameterName
scenario---------input----- long----- -------0
but still I get the same error.
June 24, 2010 at 2:49 am
Has the table dbo.test_delete_yes only 1 column?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 25, 2010 at 10:57 am
yes
June 27, 2010 at 5:18 am
Perhaps the declare is confusing it. How about this instead:
insert into dbo.test_delete_yes (col1)
values (?)
failing that, try this:
insert into dbo.test_delete_yes (col1)
values (@MyValue)
.. as different drivers use different placeholders for parameters. See here:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply