Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys. :(
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>> Hi Folks,
>> I need a script that it'll changed varchar field to nvarchar a table
>> which i
>> pass it's name via parameter.
>> I generated it but it'll raise exception when a field has foreign key
>> etc...
>> I don't want to spend more time to this work. Do you have a script like
>> this? Or program?
>> If you have it could you share with me?
>> Thanks.
>>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys. :(
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> > Hi
> >
> > If you try and change a column's data type and the column is referenced by
> > a
> > foreign key, or is part of a primary key will give you an error. You will
> > need to drop these before trying to change the columns datatype. The
> > easiest
> > way would be to script them, drop them and re-create them afterwards. To
> > drop
> > them see http://www.mssqlserver.com/scripts/drop_keys.sql
> >
> > John
> >
> > "Erencan SADIRODLU" wrote:
> >
> >> Hi Folks,
> >> I need a script that it'll changed varchar field to nvarchar a table
> >> which i
> >> pass it's name via parameter.
> >> I generated it but it'll raise exception when a field has foreign key
> >> etc...
> >> I don't want to spend more time to this work. Do you have a script like
> >> this? Or program?
> >>
> >> If you have it could you share with me?
> >> Thanks.
> >>
> >>
> >>
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easies
t
way would be to script them, drop them and re-create them afterwards. To dro
p
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which
i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc.
.
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDint
INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)
  

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help

Hi

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)