August 8, 2012 at 3:01 pm
morepainot (8/8/2012)
Dont I need to use parenthesis to open and close the alter table statement? If I delete it then ill get an error near VARCHAR
Nope that will cause an error. I also see issues with some spacing, but I don't know if that is the error Sean is trying to point you to. I didn't mean to step on any toes here, I just saw that while cruising through this thread, I'll let him help you out with the error he sees. But yes, printing the string before you execute it and even copying and pasting it in a new query editor window will help you out.
August 8, 2012 at 3:01 pm
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
August 8, 2012 at 3:03 pm
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I must agree here. When I am writing dynamic sql, this is what I do:
PRINT @SQLCmd;
--EXEC (@SQLCmd);
I can then copy what is printed and put it in an SSMS query window to parse and test. Once I have fixed any problems, I comment the PRINT and uncomment the EXEC.
August 8, 2012 at 3:06 pm
Lynn Pettis (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I must agree here. When I am writing dynamic sql, this is what I do:
PRINT @SQLCmd;
--EXEC (@SQLCmd);
I can then copy what is printed and put it in an SSMS query window to parse and test. Once I have fixed any problems, I comment the PRINT and uncomment the EXEC.
I started to recently do that but I still have problems with the errors. I think I may have solved the issue, though.
August 8, 2012 at 3:07 pm
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
_______________________________________________________________
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 8, 2012 at 3:17 pm
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
YES! It was my dynamic code that wasnt working. I didnt code it properly. Thank you tons Sean. That was the last part of my code I needed to fix. I AM DONE!!! Now I just need to format it accordingly to company standards. Thanks again, I really really appreciate it.
August 8, 2012 at 3:22 pm
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
YES! It was my dynamic code that wasnt working. I didnt code it properly. Thank you tons Sean. That was the last part of my code I needed to fix. I AM DONE!!! Now I just need to format it accordingly to company standards. Thanks again, I really really appreciate it.
You are quite welcome!!! Was it just the extra space? Yes I could have just told you about the space about 10 posts ago but I bet next time you will remember how painful it was to find such a simple mistake. Next time you will know exactly how to debug this and it will take you about 10 seconds to find the issue.
Hopefully you can get this up for a code review soon, and more importantly I hope it goes well and that you learned something along the bumpy path.
_______________________________________________________________
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 8, 2012 at 3:26 pm
I know you did a lot of the work, but I have to ask you a question.
Do you feel comfortable enough with the code you have to describe what it is doing and why it is doing it in a code review? If not, ask your questions here now to help you with your understanding.
August 8, 2012 at 3:35 pm
Lynn Pettis (8/8/2012)
I know you did a lot of the work, but I have to ask you a question.Do you feel comfortable enough with the code you have to describe what it is doing and why it is doing it in a code review? If not, ask your questions here now to help you with your understanding.
I do feel confident that I can walk through this code. I also created a flow diagram that will some what assist me. This is going to be my 1st official code review (actually 3rd if it wasnt for all the mistakes). I have all night tonight and tomorrow till 2est to work through the code and understand it all. I have a test script I am using, running that is helping me understand the code better because I see EXACTLY what everything is doing. Using the PRINT command has helped, too. I can click on the messages tab and see what each part of the code is doing. Actually, I do have a question. Its about the dynamic sql Sean helped me with. When I run my test and view the messages tab, why is it that I see that code executed twice? I see that dynamic code being run twice. If that doesnt make sense then Im just having trouble wording it.
Otherwise, thank you for all of your help and I will update you guys once the review is over. This review is sort of my plane ticket home to Chicago for a week. If I do well Ill be able to work remotely for next week.
August 8, 2012 at 3:37 pm
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
YES! It was my dynamic code that wasnt working. I didnt code it properly. Thank you tons Sean. That was the last part of my code I needed to fix. I AM DONE!!! Now I just need to format it accordingly to company standards. Thanks again, I really really appreciate it.
You are quite welcome!!! Was it just the extra space? Yes I could have just told you about the space about 10 posts ago but I bet next time you will remember how painful it was to find such a simple mistake. Next time you will know exactly how to debug this and it will take you about 10 seconds to find the issue.
Hopefully you can get this up for a code review soon, and more importantly I hope it goes well and that you learned something along the bumpy path.
Yes, it was just the extra spacing. I looked at for some time and thats all that made sense to me. Everything else was the same. I learned a lot and I really appreciate your help. Ive noticed how much my posts have changed along the way. I went from a really bad script to something Im actually pretty proud of. It took a long time but Im here now. Normally how long do project managers or directors expect a stored proc like mine to be delievered? I was never given a time frame for mine but I believe it been over 2 weeks but less than 3 weeks. Is that about right?
Thanks again.
August 8, 2012 at 3:38 pm
Can't really answer your question about double processing, can't see what you see. Also, we don't have your tables or sample data so we can't run your code to see if we can get the same problem.
August 8, 2012 at 3:38 pm
Because you already a print command in your code. In the snippet I provided I replace the exec with print because executing your dynamic code is what was causing the issue.
_______________________________________________________________
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 8, 2012 at 3:42 pm
Sean Lange (8/8/2012)
Because you already a print command in your code. In the snippet I provided I replace the exec with print because executing your dynamic code is what was causing the issue.
Okay, I just noticed that. Thanks.
August 8, 2012 at 3:42 pm
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
YES! It was my dynamic code that wasnt working. I didnt code it properly. Thank you tons Sean. That was the last part of my code I needed to fix. I AM DONE!!! Now I just need to format it accordingly to company standards. Thanks again, I really really appreciate it.
You are quite welcome!!! Was it just the extra space? Yes I could have just told you about the space about 10 posts ago but I bet next time you will remember how painful it was to find such a simple mistake. Next time you will know exactly how to debug this and it will take you about 10 seconds to find the issue.
Hopefully you can get this up for a code review soon, and more importantly I hope it goes well and that you learned something along the bumpy path.
Yes, it was just the extra spacing. I looked at for some time and thats all that made sense to me. Everything else was the same. I learned a lot and I really appreciate your help. Ive noticed how much my posts have changed along the way. I went from a really bad script to something Im actually pretty proud of. It took a long time but Im here now. Normally how long do project managers or directors expect a stored proc like mine to be delievered? I was never given a time frame for mine but I believe it been over 2 weeks but less than 3 weeks. Is that about right?
Thanks again.
The time it takes to produce something is directly related to your experience level. The more experience you get the faster you will get with this type of thing. It is pretty complicated and there is probably more art than science in devising a solution to these things. If I took that long today I would get some pretty serious looks but 10 years ago I don't think I could have pulled it off in 2-3 weeks.
_______________________________________________________________
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 8, 2012 at 3:42 pm
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
I did that and it doesnt really clarify much for me. I added the print command right after added column name .
I had to add the variables for your short snippet to work but...
declare @SourceDatabaseName varchar(50) = 'DatabaseName'
declare @nSQL varchar(max)
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
PRINT @nSQL
--EXEC sp_executeSQL @nSQL;
END
This runs just fine which means the error MUST be in your dynamic code. Take the print out:
IF NOT EXISTS (SELECT * FROM DatabaseName .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE DatabaseName.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Paste that into SSMS and run it. It fails:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Group_Name'.
See anything in your dynamic code that doesn't work?
YES! It was my dynamic code that wasnt working. I didnt code it properly. Thank you tons Sean. That was the last part of my code I needed to fix. I AM DONE!!! Now I just need to format it accordingly to company standards. Thanks again, I really really appreciate it.
You are quite welcome!!! Was it just the extra space? Yes I could have just told you about the space about 10 posts ago but I bet next time you will remember how painful it was to find such a simple mistake. Next time you will know exactly how to debug this and it will take you about 10 seconds to find the issue.
Hopefully you can get this up for a code review soon, and more importantly I hope it goes well and that you learned something along the bumpy path.
Yes, it was just the extra spacing. I looked at for some time and thats all that made sense to me. Everything else was the same. I learned a lot and I really appreciate your help. Ive noticed how much my posts have changed along the way. I went from a really bad script to something Im actually pretty proud of. It took a long time but Im here now. Normally how long do project managers or directors expect a stored proc like mine to be delievered? I was never given a time frame for mine but I believe it been over 2 weeks but less than 3 weeks. Is that about right?
Thanks again.
Viewing 15 posts - 106 through 120 (of 137 total)
You must be logged in to reply to this topic. Login to reply