I'm using partitions in my project to age data that's older than some predetermined number of days. Normal deletion of the data takes too long, but the following sequence of operations seems to work well when testing with raw sql queries:
Split (to create a new partition for new data)
Create (a new temporary table to hold the oldest partition's data)
Switch (to move the oldest partition's data into the new temporary table)
Merge (to combine the oldest two partitions, "removing" the oldest one in the process)
Drop (to drop the temporary table and all the old data that we don't want anymore)
My current problem arises at step 2, "Create." The precise column/index/etc layout of the data being aged is not be known/hardcoded into my program. I've been trying to use SMO to create a copy of an existing table into the same database with a new name, but I've been failing miserably. It may include a varbinary(max), e.g., and the "max" part doesn't seem to be exposed by Smo.Column.Datatype.
Is there a way to dynamically create a copy of a table that already exists inside the same database, changing only the name and without bringing along the table's data?
Or should I smack my DBA upside the head and tell him to put in some extra temporary tables that I can just truncate instead of making me create them dynamically?
One technique is to use a Select Into:
Select *
Into myNewTableName
From myOldTableName
Where 1=2 -- usually returns no records ;)|||Sure, I could SELECT INTO, but then what's the point of SMO? Isn't the goal, here, to avoid manually building my own query?|||
The point of SMO is that it provides an object library for you to write code to make your management of SQL Server easier for you. If something can be easily accomplished with Transact-SQL, then why not use it? It's not about platform evangelism, it's about making your job easier.
That being said, this code will bring in the HumanResources.Employee table from the AdventureWorks database and replicate it into a new table called HumanResources.NewEmp:
Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim tblExisting As Table
Dim tblNew As Table
tblExisting = db.Tables.Item("Employee", "HumanResources")
tblNew = New Table(db, "NewEmp", "HumanResources")
Dim colExistColumn As ColumnCollection
Dim clmExist As Column
colExistColumn = tblExisting.Columns
For Each clmExist In colExistColumn
Dim clmNew As Column
clmNew = New Column(tblNew, clmExist.Name)
clmNew.DataType = clmExist.DataType
clmNew.Nullable = clmExist.Nullable
tblNew.Columns.Add(clmNew)
Next
tblNew.Create()
Good luck.
|||
Thanks for the info, Allen, but I've already gone down that road. Don't forget an index loop that goes something like:
foreach (Index idx in tblOld){
String idxName = idx.Name + "_newTable"; // Or whatever, not important here
tblNew.Indexes.Add(new Index(tblNew, idxName));
tblNew.Indexes[idxName].IsClustered = idx.IsClustered;
tblNew.Indexes[idxName].IndexKeyType = idx.IndexKeyType;
tblNew.Indexes[idxName].IgnoreDuplicateKeys = idx.IgnoreDuplicateKeys;
tblNew.Indexes[idxName].IsUnique = idx.IsUnique;
foreach (IndexedColumn col in idx.IndexedColumns){
tblNew.Indexes[idxName].IndexedColumns.Add(new IndexedColumn(tblNew.Indexes[idxName], col.Name));
}
}
Recall that the tables have to be sufficiently compatible for me to switch a partition from the original into the temporary table. Creating just a table with the same column types doesn't cut it. The code sample you provide (which is nearly identical to what I wrote in my own code as well) also won't deal properly with all column data types. Try that code on a table with a type of varbinary(max), e.g. The temporary table that's created will be of type varbinary(1).
Unhandled Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException: Switch partition failed for Table 'dbo.tblOld'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: ALTER TABLE SWITCH statement failed because column 'col' has data type varbinary(max) in source table 'DbName.dbo.tblOld' which is different from its type varbinary(1) in target table 'DbName.dbo.tblNew'.
Bang. I'm dead. Do I have to create more explicit special-cased logic to handle var* types? That'd be silly.
If I have to jump through that many hoops to perform a copy operation, it tells me that copying via this technique isn't supported by the SMO libraries. I'm essentially trying to perform a manual deep-copy via a public interface. Perhaps I need to subclass the table type? Create a class "CopyableTable"? I don't think the protected methods/members will provide sufficient support for what I want either, though.
If something can be easily accomplished with T-SQL, why not use it? Company policies that have been put into place to mitigate the possibility SQL Injection would be one reason. SQL strings in the source code raise big red flags, and SMO should be able to handle this use-case.
|||
Sorry, Greg, I forgot to mention that in SMO you set the column to use varbinary(MAX) by setting the column.DataType.MaximumLength = -1
I'll be doing a demo of that in my presentation at PASS in two weeks. Your point on company policies is well taken, and I had a hunch the issue was something along those lines. I don't know why the MaximumLength property isn't carried forward to the new table - it's probably a bug, but knowing how the MAX datatype is internally coded (and I haven't found documentation about this, I just discovered it by poking around) can help resolve the problems you're facing.
Hope that helps.
|||
Thanks, Allen. I haven't tried that, so I'll give it a go and see what happens.
I agree, it's weird that MaximumLength isn't carried through.
|||
First try:
colNew.DataType.MaximumLength = colOld.DataType.MaximumLength;
Failed.
Second try:
if(colOld.DataType.MaximumLength == -1){
colNew.DataType.MaximumLength = -1;
}
Failed.
Third try:
if(colOld.DataType.MaximumLength == -1){
colNew.DataType.MaximumLength = 5;
}
Failed to set it to 5. The created table still has a varbinary length as 1. At this point I revisited the DataType interface, suspecting that these var* types must be special somehow, and I noticed some promising possibilities.
Fourth try:
if(colOld.DataType.Name == "varbinary"){
if(colOld.DataType.MaximumLength == -1)
colNew.DataType = DataType.VarBinaryMax;
else
colNew.DataType = DataType.VarBinary(colOld.DataType.MaximumLength);
}
No luck. Grrrr. I really expected that last one to work. If I replace VarBinaryMax with VarBinary(5), the tblNew.Create() method will kick out a varbinary(5). Which brings us back to your suggestion:
if(colOld.DataType.Name == "varbinary"){
if(colOld.DataType.MaximumLength == -1)
colNew.DataType = DataType.VarBinary(-1);
else
colNew.DataType = DataType.VarBinary(colOld.DataType.MaximumLength);
}
Which works. http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=662296&SiteID=17 had similar info.
Thanks again, Allen.
|||
Good technique.
I must copy also Dependencies of Stored Procedure.
Do you know anyidea ?
