August 16, 2012 at 9:15 am
First of all, I am trying to convert some crystal reports to sql reports and have been routinely submitting requests to add fields to tables in a reporting db. I do a script and run it against my test db so I know it works. All I'm doing is an...
Alter table mickey
add field_1, date, null;
Alter table mickey
add field_2, date, null;
This morning I looked at the mickey table and this is what I'm starting to see:
ALTER TABLE [dbo].[mickey] ADD [field_1] [date] NULL
ALTER TABLE [dbo].[mickey] ADD [field_2] [date] NULL
Now the fields in the mickey table are there and the reports run fine but would there be an issue with having "Alter"s in the table when it's scripted?
August 16, 2012 at 9:23 am
Where are you seeing those things? In the object tree in Management Studio? In some Crystal Reports log? In something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2012 at 9:48 am
When I do a script table and choose Create. My test db comes out fine, the production has the alters in it. I'm only asking because I need to add another field.
August 16, 2012 at 9:50 am
In Management Studio or some other tool?
I guess I'd need to see the script to verify it's correctly formatted or not. Since the table is working, it should be fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2012 at 10:15 am
here's a sample - I changed the table name to protect the innocent. This code worked fine - fields are there and scripting the table by right/clicking and selecting Create, the fields are at the end
here's the script for the table where the fields are displayed as "ALTER TABLE":
[Info_needed_letter_sent_6] [date] NULL - here is the last normal field
) ON [PRIMARY]
SET ANSI_PADDING ON
.....
ALTER TABLE [dbo].[MINNIE] ADD [Assigned_to_103] [varchar](64) NULL
.........
ALTER TABLE [dbo].[MINNIE] ADD [Docs_due_by_136] [date] NULL
ALTER TABLE [dbo].[MINNIE] ADD [MINNIE_Scheduled_138] [date] NULL
ALTER TABLE [dbo].[MINNIE] ADD [Rqst_Client_Contact_147] [date] NULL
and here's the script I sent:
(note - first script command was supposed to change the existing field to a varchar, which it did)
ALTER table Minnie
alter column Assigned_to_103 varchar(64) null;
alter table Minnie
add Docs_due_by_136 date null;
alter table Minnie
add MINNIE_Scheduled_138 date null;
alter table Minnie
add Rqst_Client_Contact_147 date null;
So what's confusing to me is how this happened. Going through other tables, it looks like rather than opening a sql query and executing it, the sysadmin did something else. Any thoughts?
August 16, 2012 at 10:36 am
Edit: What i thought below doesn't hold true; I just tested it, and I don't think it's doing what I thought it did.
Tools>>Options>>SQL Server Object Explorer>>Scripting.
I think It's the Scripting Option called "Script Change Tracking" = True
When that is checked, it includes the changes in the same order as they occurred;
if it's false, you get the traditional snapshot of the current table as it is right now.
Lowell
August 16, 2012 at 10:52 am
EDITED
Jared
CE - Microsoft
August 16, 2012 at 10:55 am
Oh...
Jared
CE - Microsoft
August 16, 2012 at 11:33 am
I don't know quite what the cross-out stuff was about but it did give me an idea as to what happened. Next time I will be more specific when I say "run this script." Clearly it was not run by copy/pasting into a query window and executing it like I thought they would. So now I have to figure out how to set up a script so when it's run in other than a query window, it adds and doesn't attach the script. Would this cause the data update process to slow down? Hmmmm
August 16, 2012 at 11:39 am
PGarberick (8/16/2012)
I don't know quite what the cross-out stuff was about but it did give me an idea as to what happened. Next time I will be more specific when I say "run this script." Clearly it was not run by copy/pasting into a query window and executing it like I thought they would. So now I have to figure out how to set up a script so when it's run in other than a query window, it adds and doesn't attach the script. Would this cause the data update process to slow down? Hmmmm
What exactly is your process here? Are you sending scripts to a DBA? Business Analyst? School Teacher? I run all of the scripts that our report writers need on our databases. However, I always ask them what they are trying to do with that script and then I rewrite it. If this person doesn't know what you are doing and clearly doesn't care what is run on the server... They should be moved to a different position or fired.
Jared
CE - Microsoft
August 16, 2012 at 11:42 am
I had speed-read your question, and assumed the problem was the GUI scripting out ALTER commands.
i completely misread your question, sorry about that. that's why i scratched it out, but left it in place.
Lowell
August 16, 2012 at 12:00 pm
It's partially my fault for not being more specific so I must share in the responsibility of the tables. I work remotely so I can't tell what's going on there. Anyway, if I go under the guise of improving performance, I think I can figure some way to fix the tables without starting a war over there in the home office. But I will be ever more descriptive in my next service request. :ermm:
August 16, 2012 at 12:54 pm
I can duplicate the issue or prevent the issue by controlling whether 'ANSI_PADDING' is on or off. For instance, if I create a table after the statement SET ANSI_PADDING ON then add a column after a statement SET ANSI_PADDING OFF, subsequently scripting a table create includes an alter statement. If I insure that the argument to SET ANSI_PADDING remains consistent, no alter statement is included when scripting another create statement from the table.
I ran into this issue when executing a 'SELECT INTO TABLE_NAME FROM...' where the from clause included a join that included two tables with different settings of ANSI_PADDING.
Just 2 cents in case you want to experiment with that and see if you are encountering that situation.
August 16, 2012 at 1:00 pm
Thanks Patrick - yes that's it. Now to carefully word the email so as not to offend....
August 16, 2012 at 1:02 pm
Thanks Patrick - yes that's it. Now to carefully word the email so as not to offend....
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply