dakota97
Well-known member
Hi all,
I have a database backup application that I wrote that connects to a remote SQL instance and copies all database changes to my local SQL Server instance. Everything is working fine, with one small glitch that does cause a problem if I need to restore the database on the remote server.
In short, the app connects to the remote database and retrieves a list off all databases on the server. It then connects to my local server and retrieves the same list. It compares the list and creates the database on the local server if need be. During this process it also creates the tables, and then populates the corresponding records. It does this for each database it finds until all databases have been processed.
The problem that I have is that when it creates the tables it doesnt set the identity property for a specific column that is set as the primary key, int, identity, etc. If I use query analyzer to generate a script of the table, I can see that the identity column is indeed specified. However, in my code I use the following line to retrieve the table structure, but it doesnt include an identity property:
Does anyone have an idea on how I can make this happen? It work be really nice if there is a way to programmatically script a table the same way it does in QA, save the script as a text file locally, and then run the script in code when I create the table. Is this possible?
Thanks in advance,
Chris
I have a database backup application that I wrote that connects to a remote SQL instance and copies all database changes to my local SQL Server instance. Everything is working fine, with one small glitch that does cause a problem if I need to restore the database on the remote server.
In short, the app connects to the remote database and retrieves a list off all databases on the server. It then connects to my local server and retrieves the same list. It compares the list and creates the database on the local server if need be. During this process it also creates the tables, and then populates the corresponding records. It does this for each database it finds until all databases have been processed.
The problem that I have is that when it creates the tables it doesnt set the identity property for a specific column that is set as the primary key, int, identity, etc. If I use query analyzer to generate a script of the table, I can see that the identity column is indeed specified. However, in my code I use the following line to retrieve the table structure, but it doesnt include an identity property:
Code:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = myTableDoes anyone have an idea on how I can make this happen? It work be really nice if there is a way to programmatically script a table the same way it does in QA, save the script as a text file locally, and then run the script in code when I create the table. Is this possible?
Thanks in advance,
Chris