January 19, 2016 at 9:04 am
I can't figure out why my attempt to compile an SP on "database1, server2" is failing when it's not failing on database1 server 1
Here are the steps:
1) I started with a database "EC" on a test vm "VM1" on an instance of SQL2012 - s2012. It has an sp "SP1"
2) I created another vm - "VM2" and setup another instance of s2012, this is to be the production server.
3) I ran the export wizard to copy table objects from EC on vm1/s2012 to ec on vm2/s2012
4) I "generated script". to create an sp,"sp1" from EC on vm1/s2012 and then executed that script on EC on vm2/s2012
5) Upon executing the script to create the sp on ec/vm2/s2012, it fails. The error and the sp are pasted below
Msg 213, Level 16, State 1, Procedure sp1, Line 13
Column name or number of supplied values does not match table definition. Rather than posting the whole sp
I will say that the statement looks like "insert into tbl_A select c1, c2 from tbl_b
and that in tbl_b the column names are slightly different and don't match in tbl_b it's c1_v1 not c1, and c2_v1 not c2. So, I would EXPECT the sp to fail at compile time on vm2
My understanding of "deferred name resolution" is that if a table is MISSING upon attempting to a compile an SP, sql won't complain, but if it is NOT MISSING but objects are missing then it will complain. So the behavior on vm2 is what I think should be expected, given my understanding of how this is supposed to work (unless I'm wrong of course).
6) However, what i don't understand is why I can successfully execute a recompile (alter procedure) of sp1 on vm1, even though in both cases the column names are mismatched. I can even recreate it under a different name on vm1 and execute a new create, even though the column names are mismatched on vm1 as well. It seems like it should fail just as it is failing on vm2, but it's not. On vm1 it will fail at run time, but not compile time.
7) I am sure that the table names and column names are the same on both vm1 and vm2.
Note that I inherited this code from another developer who is no longer with the company, so I don't know the history. For example maybe column names matched on vm1 when he originally created sp1, but then changed them later on.
The thing I don't understand is why they don't both behave the same with respect to handling the create procedure statement.
I don't know if any of this is relevant but:
a) the compatibility level is 110 on both
January 19, 2016 at 10:27 am
Check whether there is an IDENTITY property on the insert target on one of the servers, that is not present on the other server.
When not supplying a column list for INSERT (which by the way is a terrible bad practice), SQL Server will assume you meant a list of all non-IDENTITY columns.
Off the top of my head, I believe that a timestamp/rowversion column has a similar special treatment.a
January 19, 2016 at 12:59 pm
Thanks! You’re correct, they are different in this respect. Column UID on Database EC on vm2 does not have the Identity property, whereas Column UID on database EC on vm1 does. So much for my claim that I was sure they were same.
I used the export/import wizard to create the tables on vm2. In this case my judgement was that it wouldn’t be feasible to use backup/restore, so I think this is the way I need to continue to do it. (I can explain later why I don’t want to use backup/restore, if necessary, but for now I think I need to assume I’m on the right track by using export/import). I’ve now read some other articles on this and it looks to me like my mistake was to not
Check the “Enable Identity Insert” checkbox? In any event, I need to make sure that: a) the identity property is preserved by the wizard AND b) the data comes over exactly as it is in the source. Also from my experience, and what I’ve read, it’s not possible to turn a non-identity column into an identity column “after the fact”, so it looks to me like I will have to start from scratch and re-run the wizard?
Regarding not supplying a column list for the INSERT, I do realize that this is a bad practice. It makes it hard to debug and read. But I inherited the application so I will just have to adjust these types of things if time permits.
Hugo Kornelis (1/19/2016)
Check whether there is an IDENTITY property on the insert target on one of the servers, that is not present on the other server.When not supplying a column list for INSERT (which by the way is a terrible bad practice), SQL Server will assume you meant a list of all non-IDENTITY columns.
Off the top of my head, I believe that a timestamp/rowversion column has a similar special treatment.a
January 19, 2016 at 4:13 pm
Thanks for confirming my suspicion.
I'm afraid I cannot help with your questions on the import & export wizard and identity, as I never used the former, and try to avoid the latter where possible.
January 20, 2016 at 1:00 pm
Thanks Hugo.
I have this working via manual scripting, without using the wizard(s). I was unable to get the wizard to create the tables with the identity column and insert the data. Instead I just created the tables on vm2 via generate script from vm1 and changing the use reference. Then I am doing manual scripting to insert rows from tables on vm1 to vm2. I am starting with ssms standard "insert into" script and then manually tweaking it to suit my needs, but this means adding a truncate table and adding set identity_insert on, and manually adding in the identity columns for each script/table. It is all working fine but it is tedious. I would imagine there must be a faster way to do this for 20 tables? Are there third party tools or existing generic scripts that would make this easier?
The reason I have to do it this way is that the way the previous developer set things up, the database EC has two sets of tables, one for 'COMPANY1' and one for COMPANY2. Tables and live data for COMPANY1 are already on server PROD1 database EC. Tables and data for COMPANY2 are on server DEV1 database EC; I need to migrate the tables and data to server PROD1 database EC. So I can't just backup / restore, or use a copy database wizard.
Hugo Kornelis (1/19/2016)
Thanks for confirming my suspicion.I'm afraid I cannot help with your questions on the import & export wizard and identity, as I never used the former, and try to avoid the latter where possible.
January 20, 2016 at 1:44 pm
itguy2015 (1/20/2016)
Are there third party tools or existing generic scripts that would make this easier?
Not a 100% sure if it fits your issue perfectly, but I think that Red Gate's tools "SQL Compare" and "SQL Data Compare" might help you. (Especially the latter).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply