August 1, 2012 at 6:59 am
Can anyone tell me why the script below is returning "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'." when I try and execute? It compiles OK and the insert statement it generates works when I PRINT it, but I can't see what the problem is. This is the simple version - I'm eventually going to run it in a loop:
ALTER PROCEDURE [dbo].[wc_pics_flat_file_import]
AS
Declare @sqlquery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(4000)
Declare @counter_txt as NVarchar(2)
SET @counter_txt = '1'
SET @sqlquery = 'INSERT INTO [db].[schema].[tableA]
([Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,[LSC Qual Ref1]
,[A701]
,[Qual Funding Stream1]
,[Qual Programme Type1]
,[Qual Programme Entry1]
,[DeliveryMethod1]
,[Qual Fund Start1]
,[Qual Fund PED1]
,[Qual Fund Prop1]
,[Qual ALSN1]
,[Aim A711]
,[A23 Deliv PCode1]
,[A64 GrpHrs1]
,[A64 1To1Hrs1]
,[A66 Emp Status before Start1]
,[A67 Length of unemployment1]
,[Qual Fund Achv1]
,[Qual Fund End1]
,[Qual Completion Status1]
,[Qual Learning Outcome1])
SELECT [Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,[LSC Qual Ref1]
,[A701]
,[Qual Funding Stream1]
,[Qual Programme Type1]
,[Qual Programme Entry1]
,[DeliveryMethod1]
,[Qual Fund Start1]
,[Qual Fund PED1]
,[Qual Fund Prop1]
,[Qual ALSN1]
,[Aim A711]
,[A23 Deliv PCode1]
,[A64 GrpHrs1]
,[A64 1To1Hrs1]
,[A66 Emp Status before Start1]
,[A67 Length of unemployment1]
,[Qual Fund Achv1]
,[Qual Fund End1]
,[Qual Completion Status1]
,[Qual Learning Outcome1]
FROM [db].[schema].[tableB]'
SET @ParamDefinition = '@counter_txt'
EXECUTE sp_Executesql @sqlquery, @ParamDefinition, @counter_txt
end
Thanks
August 1, 2012 at 7:12 am
It's sp_Executesql that's the problem. 1) Are you using the parameter? 2) The format doesn't look right - see:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm
It works as EXEC(@SQLQuery)
August 1, 2012 at 7:36 am
mandoswork (8/1/2012)
Can anyone tell me why the script below is returning "Msg 102, Level 15, State 1, Line 1Incorrect syntax near ')'." when I try and execute? It compiles OK and the insert statement it generates works when I PRINT it, but I can't see what the problem is. This is the simple version - I'm eventually going to run it in a loop:
ALTER PROCEDURE [dbo].[wc_pics_flat_file_import]
AS
Declare @sqlquery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(4000)
Declare @counter_txt as NVarchar(2)
SET @counter_txt = '1'
SET @sqlquery = 'INSERT INTO [db].[schema].[tableA]
([Surname]
,[Firstname]
-- snip ...
EXECUTE sp_Executesql @sqlquery, @ParamDefinition, @counter_txt
end
Here is the definition of sp_executesql:
sp_executesql [ @statement = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
So your call could be put like this instead:
EXEC sp_executesql @statement = SQLQuery, @params = '@counter_txt NVARCHAR(1)'
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
August 1, 2012 at 8:43 am
mandoswork (8/1/2012)
Thanks both of you. I replaced the line:EXECUTE sp_Executesql @sqlquery, @ParamDefinition, @counter_txt
with:
EXECUTE(@SQLQuery)
and it ran. I obviously need to go and read up on this.
Thanks again
Why do you need to do this with dynamic sql? From what you have posted a direct insert makes more sense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 9:04 am
Apologies for confusing things - the original post should have been the code below. I was going to run it in a loop and increment the counter field,
ALTER PROCEDURE [dbo].[procname]
AS
Declare @sqlquery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(4000)
Declare @counter_txt as NVarchar(2)
SET @counter_txt = '1'
SET @sqlquery = 'INSERT INTO [db].[dbo].[tableA]
([Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,[LSC Qual Ref]
,[A70]
,[Qual Funding Stream]
,[Qual Programme Type]
,[Qual Programme Entry]
,[DeliveryMethod]
,[Qual Fund Start]
,[Qual Fund PED]
,[Qual Fund Prop]
,[Qual ALSN]
,[Aim A71]
,[A23 Deliv PCode]
,[A64 GrpHrs]
,[A64 1To1Hrs]
,[A66 Emp Status before Start]
,[A67 Length of unemployment]
,[Qual Fund Achv]
,[Qual Fund End]
,[Qual Completion Status]
,[Qual Learning Outcome])
SELECT [Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,[LSC Qual Ref' + @counter_txt + ']
,[A70' + @counter_txt + ']
,[Qual Funding Stream' + @counter_txt + ']
,[Qual Programme Type' + @counter_txt + ']
,[Qual Programme Entry' + @counter_txt + ']
,[DeliveryMethod' + @counter_txt + ']
,[Qual Fund Start' + @counter_txt + ']
,[Qual Fund PED' + @counter_txt + ']
,[Qual Fund Prop' + @counter_txt + ']
,[Qual ALSN' + @counter_txt + ']
,[Aim A71' + @counter_txt + ']
,[A23 Deliv PCode' + @counter_txt + ']
,[A64 GrpHrs' + @counter_txt + ']
,[A64 1To1Hrs' + @counter_txt + ']
,[A66 Emp Status before Start' + @counter_txt + ']
,[A67 Length of unemployment' + @counter_txt + ']
,[Qual Fund Achv' + @counter_txt + ']
,[Qual Fund End' + @counter_txt + ']
,[Qual Completion Status' + @counter_txt + ']
,[Qual Learning Outcome' + @counter_txt + ']
FROM [db].[dbo].[tableB]'
SET @ParamDefinition = '@counter_txt'
EXECUTE sp_Executesql @sqlquery, @ParamDefinition, @counter_txt
end
August 1, 2012 at 9:07 am
--edit--
Your structures appear to be suffering greatly from a lack of normalization.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 9:22 am
The normalisation is the whole point of the exercise. I'm trying to import a flat file, with the same columns repeated 10 times, into a more logical structure - so insert 1st set of columns, then increment the counter to specify the fieldnames, and select the next set of fields and insert them, etc.
August 1, 2012 at 9:36 am
mandoswork (8/1/2012)
The normalisation is the whole point of the exercise. I'm trying to import a flat file, with the same columns repeated 10 times, into a more logical structure - so insert 1st set of columns, then increment the counter to specify the fieldnames, and select the next set of fields and insert them, etc.
I will take your word for it. 😉 Once you need to add counters to know which column set you are working with is anything but normalized. Of course I don't know the whole process you are working here but that table structure makes me want to pop my eyeballs.
Did you get your query sorted out?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 9:45 am
I know, it's horrible working with some of the data we get here! Been useful to get my head around the sp_executesql stuff tho. And yes, I got it working, cheers.
Impressed with this forum 🙂 Really helpful.
Cheers
August 1, 2012 at 11:11 am
mandoswork (8/1/2012)
The normalisation is the whole point of the exercise. I'm trying to import a flat file, with the same columns repeated 10 times, into a more logical structure - so insert 1st set of columns, then increment the counter to specify the fieldnames, and select the next set of fields and insert them, etc.
This is probably not the best way to normalize a flat file. You're probably better off using a CROSS APPLY with a UNION ALL. Something like the following:
INSERT INTO [db].[dbo].[tableA]
([Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,i.[LSC Qual Ref]
,i.[A70]
,i.[Qual Funding Stream]
,i.[Qual Programme Type]
,i.[Qual Programme Entry]
,i.[DeliveryMethod]
,i.[Qual Fund Start]
,i.[Qual Fund PED]
,i.[Qual Fund Prop]
,i.[Qual ALSN]
,i.[Aim A71]
,i.[A23 Deliv PCode]
,i.[A64 GrpHrs]
,i.[A64 1To1Hrs]
,i.[A66 Emp Status before Start]
,i.[A67 Length of unemployment]
,i.[Qual Fund Achv]
,i.[Qual Fund End]
,i.[Qual Completion Status]
,i.[Qual Learning Outcome])
SELECT [Surname]
,[Firstname]
,[Form Number]
,[NI not formatted]
,[DOB]
,[Ethnicity]
,[Employer EDRS Ref]
,[LSC Disab]
,[Disability Code]
,[Learning Difficult]
,[Prior Attainment]
,[Prior To Learning]
,[LSC Start Emp Status]
,[LSC Curr Emp Status]
,[Prog A09]
,[Prog A10]
,[Prog A11A]
,[Prog A11B]
,[Prog A70]
,[Prog A16]
,[Prog A26]
,[Prog A27]
,[Prog A28]
,[Prog A71]
,[Prog A31]
,[Prog A34]
,[Prog A35]
,[Prog A50]
,[LSC Qual Ref]
,[A70]
,[Qual Funding Stream]
,[Qual Programme Type]
,[Qual Programme Entry]
,[DeliveryMethod]
,[Qual Fund Start]
,[Qual Fund PED]
,[Qual Fund Prop]
,[Qual ALSN]
,[Aim A71]
,[A23 Deliv PCode]
,[A64 GrpHrs]
,[A64 1To1Hrs]
,[A66 Emp Status before Start]
,[A67 Length of unemployment]
,[Qual Fund Achv]
,[Qual Fund End]
,[Qual Completion Status]
,[Qual Learning Outcome]
FROM [db].[dbo].[tableB]
CROSS APPLY (
SELECT [LSC Qual Ref1]
,[A701]
,[Qual Funding Stream1]
,[Qual Programme Type1]
,[Qual Programme Entry1]
,[DeliveryMethod1]
,[Qual Fund Start1]
,[Qual Fund PED1]
,[Qual Fund Prop1]
,[Qual ALSN1]
,[Aim A711]
,[A23 Deliv PCode1]
,[A64 GrpHrs1]
,[A64 1To1Hrs1]
,[A66 Emp Status before Start1]
,[A67 Length of unemployment1]
,[Qual Fund Achv1]
,[Qual Fund End1]
,[Qual Completion Status1]
,[Qual Learning Outcome1]
UNION ALL
SELECT [LSC Qual Ref2]
,[A702]
,[Qual Funding Stream2]
,[Qual Programme Type2]
,[Qual Programme Entry2]
,[DeliveryMethod2]
,[Qual Fund Start2]
,[Qual Fund PED2]
,[Qual Fund Prop2]
,[Qual ALSN2]
,[Aim A712]
,[A23 Deliv PCode2]
,[A64 GrpHrs2]
,[A64 1To1Hrs2]
,[A66 Emp Status before Start2]
,[A67 Length of unemployment2]
,[Qual Fund Achv2]
,[Qual Fund End2]
,[Qual Completion Status2]
,[Qual Learning Outcome2]
UNION ALL
SELECT [LSC Qual Ref3]
,[A703]
,[Qual Funding Stream3]
,[Qual Programme Type3]
,[Qual Programme Entry3]
,[DeliveryMethod3]
,[Qual Fund Start3]
,[Qual Fund PED3]
,[Qual Fund Prop3]
,[Qual ALSN3]
,[Aim A713]
,[A23 Deliv PCode3]
,[A64 GrpHrs3]
,[A64 1To1Hrs3]
,[A66 Emp Status before Start3]
,[A67 Length of unemployment3]
,[Qual Fund Achv3]
,[Qual Fund End3]
,[Qual Completion Status3]
,[Qual Learning Outcome3]
UNION ALL
SELECT [LSC Qual Ref4]
,[A704]
,[Qual Funding Stream4]
,[Qual Programme Type4]
,[Qual Programme Entry4]
,[DeliveryMethod4]
,[Qual Fund Start4]
,[Qual Fund PED4]
,[Qual Fund Prop4]
,[Qual ALSN4]
,[Aim A714]
,[A23 Deliv PCode4]
,[A64 GrpHrs4]
,[A64 1To1Hrs4]
,[A66 Emp Status before Start4]
,[A67 Length of unemployment4]
,[Qual Fund Achv4]
,[Qual Fund End4]
,[Qual Completion Status4]
,[Qual Learning Outcome4]
) AS i
I only included the first four sets of columns, but it's easy to expand this. The dynamic approach will require a separate scan of the table for each set of columns, whereas the CROSS APPLY will require only one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2012 at 11:15 am
I'm not sure, but I believe your original problem was on:
SET @ParamDefinition = '@counter_txt'
As it should be
SET @ParamDefinition = '@counter_txt as NVarchar(2)'
Just for general knowledge, as it seems that you'll be using this on a secure environment.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply