Modify column order

Hello,
I currently have a table with 2 primary keys, and 6
columsn following that. I want to add 2 new columns, that
will also be primary keys. I want to insert these new
columns in position 3 and 4.
Is there a way to do this without dropping the table and
recreating it in the correct column order ?
eg:
Table : dbo.TMCLASS has columns as follows
COUNTRYCODE (pk)
CLASS (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I want a new structure (PROPERTYTYE and SEQUENCENO added)
Table : dbo.TMCLASS
COUNTRYCODE (pk)
CLASS (pk)
PROPERTYTYPE (pk)
SEQUENCENO (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I am tryign to avoid dropping and recreating the table as
there is data at client sites.
Thanks,
AlisonDon't think so. I also don't know why you would want to. I see a few
people asking for this aesthetic change. If you qualify all your statements
instead of using * then column order is irrelevant. May look prettier in EM
or other tool but that is about it I thnk.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison Bell" <abell@.cpaglobal.com> wrote in message
news:047f01c36552$898f9d40$a601280a@.phx.gbl...
> Hello,
> I currently have a table with 2 primary keys, and 6
> columsn following that. I want to add 2 new columns, that
> will also be primary keys. I want to insert these new
> columns in position 3 and 4.
> Is there a way to do this without dropping the table and
> recreating it in the correct column order ?
> eg:
> Table : dbo.TMCLASS has columns as follows
> COUNTRYCODE (pk)
> CLASS (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I want a new structure (PROPERTYTYE and SEQUENCENO added)
> Table : dbo.TMCLASS
> COUNTRYCODE (pk)
> CLASS (pk)
> PROPERTYTYPE (pk)
> SEQUENCENO (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I am tryign to avoid dropping and recreating the table as
> there is data at client sites.
> Thanks,
> Alison
>|||I don't think there is a way either. Reason for wanting
the specific order is to match "alltables" script
generated by ERwin for data transfer at a later date. It
just means modifying the generated script each time we
make a DB change, as ERwin places the columns in pos 3
and 4 (non modifyable).
Thanks,
Alison
>--Original Message--
>Don't think so. I also don't know why you would want
to. I see a few
>people asking for this aesthetic change. If you qualify
all your statements
>instead of using * then column order is irrelevant. May
look prettier in EM
>or other tool but that is about it I thnk.|||Problem being I must do this via SQL scripting. It is
part of an upgrade script being shipped to client sites.
>--Original Message--
>easiest way is use EM's (pet's tool) table designer. Sql
>2000 will drop and create with your requirements. You
>don't have to do anything.
>>--Original Message--
>>I don't think there is a way either. Reason for
wanting
>>the specific order is to match "alltables" script
>>generated by ERwin for data transfer at a later date.
It
>>just means modifying the generated script each time we
>>make a DB change, as ERwin places the columns in pos 3
>>and 4 (non modifyable).
>>Thanks,
>>Alison
>>--Original Message--
>>Don't think so. I also don't know why you would want
>>to. I see a few
>>people asking for this aesthetic change. If you
qualify
>>all your statements
>>instead of using * then column order is irrelevant.
May
>>look prettier in EM
>>or other tool but that is about it I thnk.
>>.
>.
>|||Then you have to re-create the table (this is what EM does under covers anyhow). There is no
functionality in TSQL (or ANSI SQL, as I remember) to change column order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alison Bell" <abell@.cpaglobal.com> wrote in message news:0fe901c366bd$edc2e210$a001280a@.phx.gbl...
> Problem being I must do this via SQL scripting. It is
> part of an upgrade script being shipped to client sites.
> >--Original Message--
> >easiest way is use EM's (pet's tool) table designer. Sql
> >2000 will drop and create with your requirements. You
> >don't have to do anything.
> >
> >>--Original Message--
> >>I don't think there is a way either. Reason for
> wanting
> >>the specific order is to match "alltables" script
> >>generated by ERwin for data transfer at a later date.
> It
> >>just means modifying the generated script each time we
> >>make a DB change, as ERwin places the columns in pos 3
> >>and 4 (non modifyable).
> >>
> >>Thanks,
> >>Alison
> >>
> >>--Original Message--
> >>Don't think so. I also don't know why you would want
> >>to. I see a few
> >>people asking for this aesthetic change. If you
> qualify
> >>all your statements
> >>instead of using * then column order is irrelevant.
> May
> >>look prettier in EM
> >>or other tool but that is about it I thnk.
> >>
> >>.
> >>
> >.
> >

Modify /concatenate varbinary data in SQL Server 2005

I am trying to store large files /images in SQL Server 2005.

I have used varbinay data type for the table column. At front end i am storing value in blob and inserting record to this table. but SQL Server does not allow to insert directly blob data to varbinary . So we need to convert blob data to varbinary using convert() function. After conversion, check the data ,it gives some junk values. How do i insert blob data into table with column data type as varbinary ?

2) We are trying to send blob data to SQL server 2005 in chunks. How to do it if column in table is varbinary ?

Please advice.

Regards

Rithik

You can check the following article to get the idea.

Insert BLOB from ADO.NET

http://support.microsoft.com/kb/309158

Learn more facts about BLOB when you design database

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true

|||

Hi,

Thanks for your advice.

We are using powerbuilder 10 as front end and sql server 2005 as database. We use updateblob statement to update blob data to varbinary column in SQL Server script as follows.

1)Script :

UpdateBlob r_test Set blob_data = :lblob_filedata Where file_id =21;

2)And we used stored procedure as follows:

CRAETE PROCEDURE [dbo].[R_UPLOAD_FILES_TO_DB]
(
@.V_SOURCE_DATA VARBINARY(MAX),
@.V_FILE_ID NUMERIC(8)
)
AS

UPDATE R_TEST
SET DATA = @.V_SOURCE_DATA WHERE FILE_ID = @.V_FILE_ID

Blob data can have text files,bitmap files ,or any other file having size less than 2 GB.

The Table R_test Design :

Create table r_test

(data varbinary(max),fle_id numeric(8))

But when we checked the data we found it's not same.

SQL Server varbinary column stored blob data in different format. Why the data appears different ?

Please advice

Regards

Rithik

|||I want to store binary data in database. I have byte array, but i am unable to insert that in database because i don't know how to insert variable name of type BYTE in sql insert query.

my code is as follow

dim byteArray() as byte

strSQL = "INSERT INTO TEMP(TEMP_NAME,TEMP_BIN) VALUES('SANSHARK',?)"

how do i pass my byteArray in ?sql

modified replicated table without drop replication first

hi :
i set up a merge replication on sql server 2000.
how to modified replicated tables without dropping replication ?
Cheers
Nick
Nick,
sp_repladdcolumn and sp_repldropcolumn can be used.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||As a followup, what if a column is modified? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$roTGN%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Nick,
> sp_repladdcolumn and sp_repldropcolumn can be used.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks. i read the article but I'm still not clear on how to handle. We
are using 1 Publisher and all anonymous (laptop) subscribers and are using
Merge Subscriptions. We make changes to tables failrly often. The database
is not real large (400MB) so I want to use the easiest possible way. What
do you suggest? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OIJ9XJP%23FHA.1988@.TK2MSFTNGP12.phx.gbl...
> David,
> please take a look at this article:
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||David,
if you can afford it, the simplest method is probably to reinitialize.
Either way, you're safest by synchronizing all subscribers before undergoing
these changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Modified image filegroup never get saved

I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peter
Hi
"wodoy.peter" wrote:

> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?

> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))

> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!

> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John
|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
>
>
>
|||Hi
"wodoy.peter" wrote:

> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John
|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John

Modified image filegroup never get saved

I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.Which version of SQL 2005 are you using?
> > Problem:
> > I created a table and gave the properties "Text/Image Filegroup" the
> > value of the secondary filegroup, gave the properties "Filegroup or
> > Partition Scheme Name" the value of primary file group. After I save
> > it. Then I open it for edit again, the value of the properties
> > "Text/Image Filegroup" was not showing secondary, instead, it show
> > primary.I assume this was through SSMS? As far as I can see there is not a way of
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
> > I'd try a few times to change it, from primary to secondary, then save.
> > After that open for modify again, it still revert back to primary
> > filegroup. Seems like the changes I made to that particular properties
> > can't be saved.Creating the table with T-SQL seems to be ok!
>
> > Anyone can help?
> > Thanks in advance.
> > wodoy.peterJohn|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm using SQL Server 2005 SP1 developer edition.
> > The complete version name and number: Microsoft SQL Server 2005 -
> > 9.00.2047.00
> > Is there any reason behind MS make the properties editable in user
> > interface only, but infact, it can't be saved/changed through the
> > SSMS?This would be one for the program group! I would guess one reason would be
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John

Modified image filegroup never get saved

I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:

> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?

> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))

> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!

> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
>
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonl
y
> in SSMS (on my version (SP1))
>
>
>
>
>
>|||Hi
"wodoy.peter" wrote:

> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
>
> because once the table has data it would be a very expensive thing to chan
ge
> which could cause severe problems. But then if there is no data in the tab
le
> I don't see why it can't be changed. You may want to log this athttps://co
nnect.microsoft.com/SQLServer/Feedback
> John

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.
There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modife
d
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shahsql

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah

moderate sql procedure question

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

jagdipa wrote:

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

hi jagdipa,
we're on the same situation on that one, I've decided since then to use ado.net to update optional data, and use only stored procs if the data to be update/inserted is consistent; meaning it updates all column and not a few...

|||Once approach you could try is this:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50) = NULL,
@.new_V_Address1 varchar(50) = NULL,
@.new_V_Address2 varchar(50) = NULL,
@.new_V_Address3 varchar(50) = NULL,
@.new_V_TownCity varchar(50) = NULL,
@.new_I_RegionID bigint = NULL,
@.new_V_Postcode varchar(10) = NULL
)
AS
UPDATE T_Sites SET
[V_SiteName] = ISNULL(@.new_V_SiteName,[V_SiteName]),
[V_Address1] = ISNULL(@.new_V_Address1,[V_Address1]),
[V_Address2] = ISNULL(@.new_V_Address2,[V_Address2]),
[V_Address3] = ISNULL(@.new_V_Address3,[V_Address3]),
[V_TownCity] = ISNULL(@.new_V_TownCity,[V_TownCity]),
[I_RegionID] = ISNULL(@.new_I_RegionID,[I_RegionID]),
[V_PostCode] = ISNULL(@.new_V_Postcode,[V_PostCode])
WHERE
I_SiteID= @.old_I_SiteID

GO

|||good one terry, i've used default values before but never thought on this implementation.. sweet..|||

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

|||

meantown2 wrote:

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

hi meantown, the poster wants to update only a selected column, if he/she is going to pass all values he might update columns he doesn't want to update. The reply of terry will prevent him/her from doing that, passing only selected parameters.

|||The reason I asked this was not really for this stored procedure (I just used this one for an easy example).

I have a B2B website where there are a lot of text fields on one webform. The data entered could grow. To try to improve effieciecy, I wanted to only pass the values that have changed.

This does mean a little extra work on the webform though. I will have to go through each value and check if it has changed.

So that leads me to my next question (this is a hard question to put down in writing, but I've tried my best to put my point across):

Lets say I have loaded the old values into a webform.
Then the user changes some of these and hits Save.
Now I need to check whether the values have changed. I could do this by comparing the values against the ones in the database - but this means an extra database access to retrieve the old values. Is there a way I can use the viewstate and somehow compare the values entered by the user against the ones stored in the viewstate?

Note: I dont want to put a load of invisible input fields everywhere and save the old values in that.

Thanks in advance for any help.

Jagdip

Modelling Time Span with OLAP tools

This is more of a design question. I am trying to create a fact table for ti
me spans. For example an order might go through the following status of Crea
ted, In-Progress, Delivered. The time span between any 2 states could range
from minutes to days to mon
ths. I want to be able to see the current status and also the status as of l
ast month, or last quarter or some other previous date.
Some of the recommendations by extert sites are to have a date in your fact
table for each status accompained by a field
for storing a value 1. So the order fact by look like
Order Id
Order Create Date
Order Created Value
Order In-Progress Date
Order In-Progress Value
Order Delivered Date
Order Delivered Value
Also I am creating my own time dimension. The above approach will work very
well when you use SQLs to access your star schema, but I am not so sure this
approach will work for olap tools like MSAS.
Is there an alternative?
ThanksWhat you have here looks like an accumulating snapshot fact table. These
are best employed IMHO where you have a business process/relatively short
with milestones which you want to track.
It means that you have less rows in your fact table but that you will
revisit the row to perform UPDATEs, something not typically done.
This type of fact table is good for measuring time lags between business
processes i.e.
Time from Order -> Sale
Time from Sale -> Ship
The way I do this is to create a view over the top of the fact table which
calculates these figures for me. This way i can then use it as a measure
just like any other. I usually do it to difference in hours.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Break It" <anonymous@.discussions.microsoft.com> wrote in message
news:13CB4DFA-500D-4268-B850-5EE207F917CD@.microsoft.com...
> This is more of a design question. I am trying to create a fact table for
time spans. For example an order might go through the following status of
Created, In-Progress, Delivered. The time span between any 2 states could
range from minutes to days to months. I want to be able to see the current
status and also the status as of last month, or last quarter or some other
previous date.
> Some of the recommendations by extert sites are to have a date in your
fact table for each status accompained by a field
> for storing a value 1. So the order fact by look like
> Order Id
> Order Create Date
> Order Created Value
> Order In-Progress Date
> Order In-Progress Value
> Order Delivered Date
> Order Delivered Value
> Also I am creating my own time dimension. The above approach will work
very well when you use SQLs to access your star schema, but I am not so sure
this approach will work for olap tools like MSAS.
> Is there an alternative?
> Thanks

modeling a table with FK

I am having a problem when modeling a Foreign Key in an "Operations" table. This table holds all information on customers ′s applications and withdrawals.

Here is the structure:

CustomerID int, SourceID int, Value decimal (16,2), OperationDate datetime

Well the problem is that SourceID sometimes might be NULL depending on how the record was inserted. So its kind of cumbersome to define it as an FK, since it can be null...To get things worse, this SourceID might point to more than 1 table (depending on the CustomerType it will point to SourceA table or SourceB table)...

How should this be modeled?

What is sourceId? It sounds like you need to do a bit more normalization here and have a source table.

source
sourceId int
customerType
<other source bits>

Then your other tables like SourceA and SourceB reference this table, as well as the operations table:

create table operations
(
...I assume you have a key other than these columns,
customerId int
sourceId int null references source(sourceId)
)

Then the source will either be sourceA or sourceB depending on the customerType (assuming you are defining the source for a given customerType.) I am not sure that I am making sense, but this sounds like what I am getting from your post. If not, can you give your current table structures and a bit more about usage?

Modeling a Matrix

What is the best way to model a matrix in terms of table design? I want to
model a sociogram, which is basically a network diagram with weights
assigned to the edges. Once, established I would want to use it to do matrix
computations (matrix algebra). So, the simple version would be something
like the following:
0 1 1
1 0 1
1 1 0
The rows and columns will constitute the same set of data. In other words,
the matrix will reflect the relationship between like entities from the same
set. Relationships between persons of a given set of people, for example.
Just wondering if there is a clever way of modeling this, or if I should
just use a table with three columns: entityA, entityB, edgeValue...
Thanks
BKGet a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
in SQL.
CREATE TABLE Martix
(i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
element_value FLOAT NOT NULL,
PRIMARY KEY (i, j, k));
Then you talk about graphs in SQL with a sociogram -- which is it?|||(grabs SQL FOR SMARTIES off of his bookshelf...and there it is on p303! ..
should have checked there first)
Excellent, thanks! I thought that might be the best way to do, but just
wanted to validate my gut feeling...
The answer to your question is: both. Really its just a matrix
representation of a sociogram, so the edges will represent the relationships
between the nodes (and the elements of the matrix). The row and column
vectors will be the same and will represent the "people".
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115680013.666216.159290@.f14g2000cwb.googlegroups.com...
> Get a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
> in SQL.
> CREATE TABLE Martix
> (i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
> j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
> k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
> element_value FLOAT NOT NULL,
> PRIMARY KEY (i, j, k));
> Then you talk about graphs in SQL with a sociogram -- which is it?
>|||BK,
See http://groups.google.co.uk/groups?q=BBEB95_9DBB7E for an example.
Steve Kass
Drew University
BK wrote:

>What is the best way to model a matrix in terms of table design? I want to
>model a sociogram, which is basically a network diagram with weights
>assigned to the edges. Once, established I would want to use it to do matri
x
>computations (matrix algebra). So, the simple version would be something
>like the following:
>0 1 1
>1 0 1
>1 1 0
>The rows and columns will constitute the same set of data. In other words,
>the matrix will reflect the relationship between like entities from the sam
e
>set. Relationships between persons of a given set of people, for example.
>Just wondering if there is a clever way of modeling this, or if I should
>just use a table with three columns: entityA, entityB, edgeValue...
>Thanks
>BK
>
>

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

sql

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

model databases

Hi,

When i create a new table in Model database how can i automatically create table in other user databeses. Thanks for your answers

FYI, any object you create in the model databas will be automatically created in any user database you create in the future. So say, you create a table named Table1 in your model database, there will be a table named Table1 in all the user databases you create after that|||

The model database is the mold for future databases that are created. There is no mechanism in place to have other databases "inherit" the structures that are put into model after the database is created.

You would want to either A. Run the create script yourself in existing databases or B. Write a tool to cursor through all databases (possibly using sp_MSforeachdb, which is undocumented, but you can find an abundence of info on the web) and create the objects like that.

I would build something in a UI tool that logs your actions personally, as you should need to do this in dev, test, preprod, and/or prod at some time.

MMC.EXE -Application Error

--Using SQL Server 2000 standard edition with SP3 on Windows 2000 Server
I am getting an error when I try to copy the data from a table to a text fil
e using a DTS Package. I am simply creating a connection to the database (s
ource) where the table exists and then creating another connection to the te
xt file (destination). The
properties of the text file are comma for the column delimiter, double quote
s for the text qualifers and first row contains column names. So after crea
ting the two connections I then create the 'Transform Data Task' (black arro
w going from the source to
the destination). I then go to the properties of the 'Transform Data Task'.
On the 'Source' tab I select the table I want to copy. I can preview the
data with no problems. I then click on the 'Destination' tab to define the
colmns. I click 'populate
from source' (it populates from the source) and then click 'execute'. This
is where the problem happens. It doesn't define the columns in the 'Destina
tion' tab. It is blank in the white space below. When I click the 'Define
Columns' button to try def
ine the columns again, I receive the following error in a pop up window:
In the Title Bar of the pop up window it says: MMC.EXE - Application Error
In the main part of the window it says: The instruction at 0x4173d23a" refe
renced memory at "0x01521e90". The memory could not be "written". Click on
OK to terminate the program
I click OK, and then it boots me out of Enterprise Manager.
In the 'disconnected edit' properties for the text file destination connecti
on, I have set the OLE DB Properties 'max characters per delimited column' f
rom 255 to 8000 to set if that is the problem since I have some columns that
are varchar 255. Still ge
t the same error.
I appreciate any help in advance to try resolving this problem.This error occurs when you apply SP3 or SP3a to your server. Article 814113
addresses this problem and then tells you to go to article 821277 for the p
atch downloads. You have to apply a cumulative security patch which creates
a problem with passwords o
n sql logins so you have to apply a small patch to fix that. I applied both
hotfixes in a test environment and it fixed the error. Hopefully it doesn'
t break anything else. Also, you have to apply these hotfixes to sql server
2000 with SP3 or SP3a and
also to workstations who are just running the client.
"IKE" wrote:

> --Using SQL Server 2000 standard edition with SP3 on Windows 2000 Server
> I am getting an error when I try to copy the data from a table to a text file usin
g a DTS Package. I am simply creating a connection to the database (source) where t
he table exists and then creating another connection to the text file (destination).
T
he properties of the text file are comma for the column delimiter, double qu
otes for the text qualifers and first row contains column names. So after c
reating the two connections I then create the 'Transform Data Task' (black a
rrow going from the source
to the destination). I then go to the properties of the 'Transform Data Tas
k'. On the 'Source' tab I select the table I want to copy. I can preview t
he data with no problems. I then click on the 'Destination' tab to define t
he colmns. I click 'popula
te from source' (it populates from the source) and then click 'execute'. Th
is is where the problem happens. It doesn't define the columns in the 'Dest
ination' tab. It is blank in the white space below. When I click the 'Defi
ne Columns' button to try d
efine the columns again, I receive the following error in a pop up window:
> In the Title Bar of the pop up window it says: MMC.EXE - Application Erro
r
> In the main part of the window it says: The instruction at 0x4173d23a" re
ferenced memory at "0x01521e90". The memory could not be "written". Click
on OK to terminate the program
> I click OK, and then it boots me out of Enterprise Manager.
> In the 'disconnected edit' properties for the text file destination connection, I
have set the OLE DB Properties 'max characters per delimited column' from 255 to 800
0 to set if that is the problem since I have some columns that are varchar 255. Sti
ll
get the same error.
> I appreciate any help in advance to try resolving this problem.
>
>

MMC.EXE -Application Error

--Using SQL Server 2000 standard edition with SP3 on Windows 2000 Server
I am getting an error when I try to copy the data from a table to a text file using a DTS Package. I am simply creating a connection to the database (source) where the table exists and then creating another connection to the text file (destination). The
properties of the text file are comma for the column delimiter, double quotes for the text qualifers and first row contains column names. So after creating the two connections I then create the 'Transform Data Task' (black arrow going from the source to
the destination). I then go to the properties of the 'Transform Data Task'. On the 'Source' tab I select the table I want to copy. I can preview the data with no problems. I then click on the 'Destination' tab to define the colmns. I click 'populate
from source' (it populates from the source) and then click 'execute'. This is where the problem happens. It doesn't define the columns in the 'Destination' tab. It is blank in the white space below. When I click the 'Define Columns' button to try def
ine the columns again, I receive the following error in a pop up window:
In the Title Bar of the pop up window it says: MMC.EXE - Application Error
In the main part of the window it says: The instruction at 0x4173d23a" referenced memory at "0x01521e90". The memory could not be "written". Click on OK to terminate the program
I click OK, and then it boots me out of Enterprise Manager.
In the 'disconnected edit' properties for the text file destination connection, I have set the OLE DB Properties 'max characters per delimited column' from 255 to 8000 to set if that is the problem since I have some columns that are varchar 255. Still ge
t the same error.
I appreciate any help in advance to try resolving this problem.
This error occurs when you apply SP3 or SP3a to your server. Article 814113 addresses this problem and then tells you to go to article 821277 for the patch downloads. You have to apply a cumulative security patch which creates a problem with passwords o
n sql logins so you have to apply a small patch to fix that. I applied both hotfixes in a test environment and it fixed the error. Hopefully it doesn't break anything else. Also, you have to apply these hotfixes to sql server 2000 with SP3 or SP3a and
also to workstations who are just running the client.
"IKE" wrote:

> --Using SQL Server 2000 standard edition with SP3 on Windows 2000 Server
> I am getting an error when I try to copy the data from a table to a text file using a DTS Package. I am simply creating a connection to the database (source) where the table exists and then creating another connection to the text file (destination). T
he properties of the text file are comma for the column delimiter, double quotes for the text qualifers and first row contains column names. So after creating the two connections I then create the 'Transform Data Task' (black arrow going from the source
to the destination). I then go to the properties of the 'Transform Data Task'. On the 'Source' tab I select the table I want to copy. I can preview the data with no problems. I then click on the 'Destination' tab to define the colmns. I click 'popula
te from source' (it populates from the source) and then click 'execute'. This is where the problem happens. It doesn't define the columns in the 'Destination' tab. It is blank in the white space below. When I click the 'Define Columns' button to try d
efine the columns again, I receive the following error in a pop up window:
> In the Title Bar of the pop up window it says: MMC.EXE - Application Error
> In the main part of the window it says: The instruction at 0x4173d23a" referenced memory at "0x01521e90". The memory could not be "written". Click on OK to terminate the program
> I click OK, and then it boots me out of Enterprise Manager.
> In the 'disconnected edit' properties for the text file destination connection, I have set the OLE DB Properties 'max characters per delimited column' from 255 to 8000 to set if that is the problem since I have some columns that are varchar 255. Still
get the same error.
> I appreciate any help in advance to try resolving this problem.
>
>

Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie