Default constraints

Hello

I have about 40 tables where I need to increase the size of the admission number field from smallint to Int . The field used the tables' primary key or part of the key. I am a programmer, but fairly new to SQL Server. I have written some scripts to remove the defaults and primary key constraints off of this field in each table, do the field resize and then put the constraints back on. The scripts get the names and settings of the constraints from system tables before the constraints are dropped, so that they can be reapplied after the field size change.

Is this the best way to do it? Or should I be looking at a DTS package?

I would be grateful for your advice

Shirley

To me (unless you have a database design tool), this sounds like a sufficient way to do it. It is never perfectly easy or anything, but you can get most everything you need to generate a script using the system tables, so I would do that. Sounds messy of course, since you also have to deal with foreign key constraints, but if it is just 40 tables, that probably isn't too bad. (I assume you will make ths size plenty big for the next sixty years this time :)

|||

Thanks very much for that. Yes, Integer will definitely be a big enough field size for the forseeable future for our admission number!

Shirley

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
Vitaliy
I'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan
|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last
>
|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>
|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first p
lace, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl..
.
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
..
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
..
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last[vbcol=seagreen]
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the cu
rrent name of the
constraint. You can then use dynamic SQL to drop the constraint. And then ad
d it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations whe
n
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>sql

Deduping DB

I have an Sql database with several tables. It has about 75,000 records in it but also has a lot of dupes. Can anyone help me with a script to isolate the most possible dupes? One of the most common things I'm seeing is mispelled names.
Thanks.Check out this post:

http://www.dbforums.com/t1001522.html|||A good starting point would be to quickly link an Access front-end to your DB and then run the Access Find Duplicates query wizard to get an idea of how it does it. Unfortunately there is absolutely no way you are ever going to remove all duplicates from a database. I have had quite a lot of experience with this - NEVER promise anyone that you can do it!

One of the problems with removing cutomer/supplier dups is that if the duplicate customer/supplier also has records in other related tables you will then want to link that data to the duplicate you are keeping if u know what I mean...

Cheers|||I've tried access and the number I am getting seems unbelieveable. Do you know of any tools that might work?|||Hi,

There is software in the marketplace that will do this sort of thing but it all depends on how conisitently your data has been entered in the first place e.g. some users might enter 'Mr Matt McDonald' into a name field whilst others might enter 'Matt McDonald' - a standard database de-duping routine obviously wouldn't pick this up.

Whilst working for a mailing house and de-duping customer data I used software developed by QAS (www.qas.com) but it is very expensive and only works well if you have consistent customer address fields as it looks for postcodes etc. - as far as I'm aware most of the de-duping software works like this.

You say that Access returns more records that you think it should - maybe you need to change the criteria to specify what really are duplicates. Have you tried double-checking some of the results to see if they really are duplicates?? Other than that the only way round this is to write your own custom procedure using a combination of code (maybe VBA in access??) and Queries using wildcard characters e.g. Like * *

I don't know if this is any help to you but de-duping is different for each organisation and for each set of data...there is no one template fits all.

Matt|||Sure...you'll get an answer very fast if you post the DDL

AND what you consider a dup to be...it's not always black and white with some people

A dup to me is everything on the row is exactly the same...

Not just the PK

If you have a PK...which you don't because then you wouldn't have a dup

declaring cursors

I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

EXECUTE Select + @.LastNameField + ', ' + @.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' + @.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

so you'll can know if your statement is correct.

bye,
Maritzita

Originally posted by plineham
I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3|||To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

Thanks for your help|||Great...cursors are bad enough, noe we have dynamicx sql cursors...

what are you trying to do?

I hope it's an admin function...

(as opposed to an applcation function)

I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

so...

why bother?|||We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me|||You dude..no problem...just trying to help you..

What do you mean by building keys?

And this..

Building the keys within SQL would be even more complicated.

Doesn't make sense to me...

mostly because I don't understand what building keys means...

In any event...I hope it's working for you...

AND it's FRIDAY...yeeeeHaaaaaaa

Now if I can find my lost shaker of salt, I'd be in business..|||It was friday but it is now monday. Aaaaaaa not good at all.

The key I mean is essentially a string containing key components of an address

for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

Thanks for your help anyway|||What are the rules to build a "key" and how do you update it?

Sounds like a job for a user defined function.

If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

Can you post some VB code?|||Find attached the classes we use to build some of the keys we use for address manipulation.

If you know a way to build all these keys into separate fields through SQL I will be very grateful

DECLARE in SQL CE

Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

You do not have to specify a value for fields with primary key/identity constraints. See this help topic for more info:

http://msdn2.microsoft.com/en-us/library/ms174633.aspx

|||Sorry, I didn't mean primary key constraints. I meant, how do I insert into Table1 if it has a Column which is a Foreign Key referencing Table2?

Matt
|||If you are referring to IDENTITY columns, you can query "SELECT @.@.IDENTITY" (on the same open connection where you just did the INSERT), and use this value as the foeign key in the next INSERT.

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19,12) and my result gets rounded around the 5th place of scale. I've tried CASTing, changing sizes on the column(s) and I still seem to get the rounding.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error converting numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ([factorAID] [int] IDENTITY (1, 1) NOT NULL ,[factorA_amt] [decimal](38, 12) NULL ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorB] ([factorBID] [int] IDENTITY (1, 1) NOT NULL ,[factorB_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorC] ([factorCID] [int] IDENTITY (1, 1) NOT NULL ,[factorC_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorD] ([factorDID] [int] IDENTITY (1, 1) NOT NULL ,[factorD_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
FWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my calculations come out correct. I guess that the arithmetic of the table shown in BOL (and below) really needs to be thought through (although I saw a post here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19
,12) and my result gets rounded around the 5th place of scale. I've tried C
ASTing, changing sizes on the column(s) and I still seem to get the rounding
.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error convertin
g numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I
tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ( [factorAID] [int] IDENTITY (1
, 1) NOT NULL , [factorA_amt] [decimal](38, 12) NULL ON [PRIMAR
Y]
GO
CREATE TABLE [dbo].[factorB] ( [factorBID] [int] IDENTITY (1
, 1) NOT NULL , [factorB_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorC] ( [factorCID] [int] IDENTITY (1
, 1) NOT NULL , [factorC_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorD] ( [factorDID] [int] IDENTITY (1
, 1) NOT NULL , [factorD_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS D
ECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GOFWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my cal
culations come out correct. I guess that the arithmetic of the table shown
in BOL (and below) really needs to be thought through (although I saw a post
here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Resu
lt scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1
+ s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 +
p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a resu
lt precision is greater than 38, the corresponding scale is reduced to preve
nt the integral part of a result from being truncated

deceptively simple join / select question

Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?

Originally posted by blm14_cu
Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?|||Originally posted by JODonnell
Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?

No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

pname1 cname1
pname1 cname2

I want:

pname1 cname1 cname2|||Originally posted by blm14_cu
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

pname1 cname1
pname1 cname2

I want:

pname1 cname1 cname2

What about GROUP BY:

Select p.*,c.* From Ptable P Join Ctable C ON P.pid = C.pid Where [P.pid = C.pid] GROUP BY P.pid

Sorry for the mess but it's almost 5.

John|||Still no good. The group by wont help because I'm not doing any sums or avgs or counts or anything. Adding the group by wont change the results at all actually, from what I know.|||I was bored.

I think this is what you're looking for

Rgds,
Jim.

declare @.d_id int;
declare @.c_name varchar(100);
declare @.c_arr varchar(2000);
declare @.tmp varchar(100);

declare @.x table([id] int, [name] varchar(2000))

DECLARE d cursor for
select depid
from dept;

OPEN d
FETCH NEXT FROM d INTO @.d_id
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.tmp='';
set @.c_arr='';

DECLARE c CURSOR FOR
SELECT name
FROM emp
where deptid = @.d_id

OPEN c
FETCH next from c into @.c_name
while @.@.fetch_status = 0
BEGIN
print @.d_id
print @.c_name

set @.tmp = @.c_arr
set @.c_arr = @.c_name+','+@.tmp
fetch next from c into @.c_name
END
CLOSE c
DEALLOCATE c
if (len(@.c_arr)>1)
begin Insert @.x values(@.d_id, substring(@.c_arr,1,len(@.c_arr)-1))end

FETCH NEXT FROM d INTO @.d_id
END
CLOSE d
DEALLOCATE d

select id, name as name from @.x
GO|||You might check yesterday's thread (http://www.dbforums.com/t989683.html) on this topic.

-PatP

Debugging stops without messages

Have a task that has 120 tables (components) that I am running in debug mode. Just over half of the components run which takes btrieve db and converts into a sybase db. When it stops running there are a few components that are yellow, the components which completed are green and the rest are still white because they have ran yet. The problem is there is not a message to indicate as to why it stopped. I've broken up the task into two tasks and also tried making two projects. The same situation happens at the same point. Our dbas have checked the database to ensure that's fine and it is. Is there some sort of limitation in how many components can be run in debug mode?

No, but enginethreads may be limiting you here. Read this and see if it helps: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx

-Jamie

|||Thanks for the response. I read the link and tried making a few changes to the enginethreads but no luck. In my original project I had two tasks that were linked and the first one ran without issues and the next task is where it only did the couple of tables. I created a new project and added the package to the new project then deleted the first task. Now that it's a separate project I still have the same issue. Because I copied the original package could there still be some sort of hooks that won't change because of the copy and it will still associate the number of components with the original package? The reason for this question is that I changed the enginethreads to be the max of 60 and when I ran it came back with a message that the required amount of threads in the pipeline were 121 and the max allowed was 64. I was thinking that because the package was copied could the pipeline info still show as 121 instead of the actually component count? Each task originally had about 60 components. Can I delete lines from the xml file that the package creates?|||

I found this log:

04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 3368
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0017.mdmp
04/04/06 14:46:43, ACTION, DtsDebugHost.exe, Watson Invoke: No

|||

That looks like it could be a SQL Server issue - that's where SQLDUMPER files come from unless I'm mistaken.

-Jamie

|||

Thanks again Jamie. With my post before the log info just wondered what your opinion was on that? I read the link and tried making a few changes to the enginethreads but no luck. In my original project I had two tasks that were linked and the first one ran without issues and the next task is where it only did the couple of tables. I created a new project and added the package to the new project then deleted the first task. Now that it's a separate project I still have the same issue. Because I copied the original package could there still be some sort of hooks that won't change because of the copy and it will still associate the number of components with the original package? The reason for this question is that I changed the enginethreads to be the max of 60 and when I ran it came back with a message that the required amount of threads in the pipeline were 121 and the max allowed was 64. I was thinking that because the package was copied could the pipeline info still show as 121 instead of the actually component count? Each task originally had about 60 components. Can I delete lines from the xml file that the package creates?

Once again thanks for your responses.

Debug stored procedure that uses comma delimited list to insert multiple records

I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:

exec proc_Add_Product_Files @.FileName = N'This is just a test.doc', @.FileDescription = N'test', @.FileSize = 24064, @.LanguageID = NULL, @.DocumentCategoryID = 1, @.ComplianceID = NULL, @.SubmittedBy = N'Kevin McPhail', @.SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @.ProductID = N'10,11,8'

Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)

CREATE PROCEDURE proc_Add_Product_Files_v2
/*
Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a row
into productfiles for each product that the file being uploaded pertains to.
*/
@.FileName varchar(150),
@.FileDescription varchar(150),
@.FileSize int,
@.LanguageID int,
@.DocumentCategoryID int,
@.ComplianceID int,
@.SubmittedBy varchar(50),
@.SubmittedDate datetime,
@.ProductID varchar(150)

AS
BEGIN

DECLARE @.FileID INT

SET NOCOUNT ON

/*
Insert into the files table and retrieve the primary key of the new record using @.@.identity
*/
INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate)
Values
(@.FileName, @.FileDescription, @.FileSize, @.LanguageID, @.DocumentCategoryID, @.ComplianceID, @.SubmittedBy, @.SubmittedDate)

Select @.FileID=@.@.Identity

/*
Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.
*/
DECLARE @.ProductFilesInsert varchar(2000)

SET @.ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@.FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @.ProductID + ')'

exec(@.ProductFilesInsert)

End
GO

I created your stored procedure locally, and did a PRINT of @.ProductFilesInsert and all looks good to me. Setting @.FileID = 0 instead of selecting its value to be @.@.Identity, this is what @.ProductFilesInsert contains, and that is syntactically correct:

INSERT INTO ProductFiles (FileID, ProductID) SELECT 0, Product1ID FROM Products WHERE Product1ID IN (10,11,8)

Your stored procedure is named proc_Add_Product_Files_v2, yet you are executing proc_Add_Product_Files. Is the problem simply that your are executing an old version of your stored procedure?|||

Terri:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

Thanks again,

Kevin

|||

Kevin.McPhail wrote:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

It wasn't obvious to me. The only reason I noticed was that exec proc_Add_Product_Files failed failed for me because I didn't have the original in place :-) I can't tell you how many times I've been burned by the very same thing.

For what it's worth, I am not a big fan of dynamic SQL, especially when an alternate methodology is possible. You could use this approach instead:

INSERT INTO
ProductFiles
(
FileID,
ProductID
)
SELECT
@.FileID,
Product1ID
FROM
Products
INNER JOIN
dbo.Split(@.ProductID,',') AS A ON Products.Product1ID = A.Element

There are many variations of a "split" function. Here's one that Dinakar provided in this thread:http://forums.asp.net/989365/ShowPost.aspx:

CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END|||

Thanks again Terri! I had been looking for a good understandable (not a sql guru) way to pass a delimited string or array to sql for inserts. I read through a couple articles i found that left my head spinning and decided to go with the old dynamic sql method since i at least understood what it did. Your example(and Dinakar and Karen's ) is exactly what i had been looking for.

Thanks,

Kevin