August 8, 2012 at 3:43 pm
Lynn Pettis (8/8/2012)
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.
I had a print commant in twice.
August 8, 2012 at 3:47 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)
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.
Ok that makes me feel a little better. Im a pretty junior level guy at this office. Age wise im the youngest, experience wise I have the least. But I managed to pull this off. Ill be reviewing this code in from of my director, 3 senior guys ( 15+ years each), 5 developers (more than 5+ years each) and the project lead. They say its informal but I dont think something like a code review can be informal.
August 8, 2012 at 3:49 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.
Why is that we dont need exec the dynamic code? For all the other dynamic I have I also have a exec at the end of it.
August 8, 2012 at 3:51 pm
morepainot (8/8/2012)
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.Why is that we dont need exec the dynamic code? For all the other dynamic I have I also have a exec at the end of it.
LOL you do need to execute it. You couldn't debug it though because you were trying to execute it and it failed. You will need to change your print back to an exec. You can't debug the code when it crashes. 😛
_______________________________________________________________
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:57 pm
Sean Lange (8/8/2012)
morepainot (8/8/2012)
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.Why is that we dont need exec the dynamic code? For all the other dynamic I have I also have a exec at the end of it.
LOL you do need to execute it. You couldn't debug it though because you were trying to execute it and it failed. You will need to change your print back to an exec. You can't debug the code when it crashes. 😛
Okay, then I still have the problem. When I took out the exec it looks like stored proc works. When I add it doesnt. I ran the dynamic in another window and I still get the same error.
August 8, 2012 at 4:03 pm
Can you post the code, and the exact error message you are getting?
August 8, 2012 at 4:07 pm
Lynn Pettis (8/8/2012)
Can you post the code, and the exact error message you are getting?
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
Error messages;
Incorrect Syntax near '('
Incorrect Syntax near 'Group_Name'
August 8, 2012 at 4:24 pm
morepainot (8/8/2012)
Sean Lange (8/8/2012)
morepainot (8/8/2012)
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.Why is that we dont need exec the dynamic code? For all the other dynamic I have I also have a exec at the end of it.
LOL you do need to execute it. You couldn't debug it though because you were trying to execute it and it failed. You will need to change your print back to an exec. You can't debug the code when it crashes. 😛
Okay, then I still have the problem. When I took out the exec it looks like stored proc works. When I add it doesnt. I ran the dynamic in another window and I still get the same error.
Okay.. 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
PRINT 'completed step: 10'
Errors;
Incorrect syntax near '('
Incorrect syntax near 'Group_Name'
August 8, 2012 at 4:41 pm
First of all, you didn't provide the complete error messages, no line numbers. Second, this is the sql I have printed:
IF NOT EXISTS (SELECT * FROM MyDatabase .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'Grp_name') ALTER TABLE MyDatabase.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
Copy it into a SSMS query window, hit the check mark (or ctrl-e). You should get two error messages. If you double click the error message, it will take you to the line for that error.
I can tell you that it looks like you have two parens you don't need. Look at the syntax for the ALTER TABLE statement in Books Online.
August 8, 2012 at 4:42 pm
Also, the code you posted still has a space where you don't want one.
August 8, 2012 at 4:57 pm
Hoping you are working on this, but thought I should post what I have done.
DECLARE @SourceDatabaseName NVARCHAR(128) = 'MyDatabase';
DECLARE @nSQL NVARCHAR(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
PRINT 'completed step: 10';
August 8, 2012 at 7:20 pm
Lynn Pettis (8/8/2012)
Hoping you are working on this, but thought I should post what I have done.
DECLARE @SourceDatabaseName NVARCHAR(128) = 'MyDatabase';
DECLARE @nSQL NVARCHAR(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
PRINT 'completed step: 10';
Ya, I was working on it. I couldnt come up with anything that seemed to work. Im not at the office anymore but ill put the code you gave me tomorrow morning. I dont see much of a different, except for maybe the spacing and parenthesis. Does spacing really factor in that much?
August 8, 2012 at 7:26 pm
Turns out the space may not matter (tested here at home), but the parens violated the ALTER TABLE syntax.
Regarding the space, it may really come down to being consistent when writing your queries. Personally, I prefer not seeing them there as it just looks "wrong."
August 8, 2012 at 7:37 pm
Lynn Pettis (8/8/2012)
Turns out the space may not matter (tested here at home), but the parens violated the ALTER TABLE syntax.Regarding the space, it may really come down to being consistent when writing your queries. Personally, I prefer not seeing them there as it just looks "wrong."
Ok, thanks, ill try it out at the office. I showed one of the senior guys the code and they said the same thing you said. I will format the code once I figure out this syntax mess. I have to go through the entire code to format it and comment the print commands. I still have a good amount to do, thankfully its just cleaning up.
August 8, 2012 at 8:24 pm
morepainot (8/8/2012)
I showed one of the senior guys the code and they said the same thing you said.
??? :blink:I thought you said you didn't have anyone who could help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 121 through 135 (of 137 total)
You must be logged in to reply to this topic. Login to reply