Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing DatasetName and Datasource programatically

Hello,
Does anyone know if it's possible to programatically change the
datasetName and/or dataSource via either the reportViewer.dll or
through report parameters?
My situation is that I need to be able to switch the database and query
statement from an active database to an archive database, based on a
user selection of whether or not they want to report off the
active/archived database. The report itself and the fields used in it
are identically named in both the archive and active database.
Thus, I'm trying to avoid having to create a duplicate report where the
only difference is the datasource/dataset. Is this possible?
Thank you for any help!You could write a custom data processing extension (DPE). There's probably
an easier way but that would definitely work... althought it isn't
straightfoward or simple.
"kevin.haynie@.gmail.com" wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!
>|||Hi Kevin,
The easiest way is through report parameters, as you said. All you have
to do is build conditional code around the parameters, and make it
select which DB the user wants to see a report of.
Lance M.
kevin.haynie@.gmail.com wrote:
> Hello,
> Does anyone know if it's possible to programatically change the
> datasetName and/or dataSource via either the reportViewer.dll or
> through report parameters?
> My situation is that I need to be able to switch the database and query
> statement from an active database to an archive database, based on a
> user selection of whether or not they want to report off the
> active/archived database. The report itself and the fields used in it
> are identically named in both the archive and active database.
> Thus, I'm trying to avoid having to create a duplicate report where the
> only difference is the datasource/dataset. Is this possible?
> Thank you for any help!

Changing data source programatically does not transfer data

I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.

The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!

Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?

I think this may be the wrong approach. You can parameterize the package at execution time with the connection string of the source and destination. This is better, and easier, than modifying the package structure.

-Jamie

|||

That is exactly what I am doing Jamie. I am changing the Connection string property of the source connection. Still doesn't work. Check below the code snippet:

// Load the package

string sLPFPackage = @."C:\projects\CCS_DTS\CCS_DTS\LPFMigration.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package oLPFPackage = app.LoadPackage(sLPFPackage, null);

oLPFPackage.UpdateObjects = true;

// Modify the source and target data locations

sDataSource = "Data Source=" + sLPFDataFolder + ";Provider=VFPOLEDB.1;Collating Sequence=MACHINE;";

ConnectionManager oDataConn = oLPFPackage.Connections["SourceConnectionOLEDB"];

DtsProperty prDataSource = oDataConn.Properties["ConnectionString"];

prDataSource.SetValue(oDataConn, sDataSource);

// Execute the package

oLPFPackage.Execute();

Any ideas?

|||

Actually that is exactly what I am suggesting you do NOT do. The package can be parameterized from the command-line when executed via dtexec.exe without actually changing the package itself. I don't know much about executing package from an application but I have no doubt that you will be able to do the same.

Explore the /SET option of dtexec.exe for more information: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

-Jamie

|||Thanks jamie. Let me check it out. I will let you know if it helped.