Data tarsfer from one table to another with different schema on sqlserver 2000

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
Thanks
ABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
|||On Mar 16, 8:01Xam, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> X I am working on SqlServer 2000,
> XWhen row will be inserted/updated Xin one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?
sql

Data tarsfer from one table to another with different schema on sql

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
ThanksABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks|||On Mar 16, 8:01=A0am, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> =A0 I am working on SqlServer 2000,
> =A0When row will be inserted/updated =A0in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?

Data sequence in tables, please help

I'm new to sql server 2005. Previously I used Access. I've noticed that in sql server when a record is inserted into the database table, the data does not get entered sequentially. I looked at the id column and saw that the order numbers are mixed up. Is this something that is common in sql server, and how can I change this. I need the data to be ordered in the order that it was inserted into the database.

Can someone please help.

SQLServer does not enforce any order of data for storage purposes.

If you wish to have the data presented in a sorted, or ordered, scheme, you MUST use an ORDER BY on the query.

IF you have an IDENTITY field (Column) in the table, it will sequentially number all rows that are added to the table. Verify that your table has the [ID] column defined as IDENTITY.

Also, you may wish to review the list of reserved words to be sure that you do not name tables and columns with words that have special meaning for SQL Server. You may also wish to avoid common words for your Table and Column names in order to reduce confustion. You will have to use square brackets anytime your table or column names violate the reserved word list.

Refer to Books Online, Topic: 'Reserved Words [SQL Server]'

|||

it seems like your table is a heap. ie. there is no clustered index. in that case the data will be in the order how u have inserted. In a relational database it does not really matter since while selecting you can order the records set. But first of all you need to define a primary key for this table and you should create a clustered index(not necessary to create on PK) .

Madhu

Data Pump

My client is using a sql server 7.0 to store realtime data like heat,temp,pressure etc inserted every second.He wants me to provide a solution to transfer the summarised data to oracle server on a regular basis..say once on every 5 min..
what approach i should take...help me out plsSet up a DTS package to send the data from SQL Server to Oracle.
Create a scheduled job to run the package every five minutes.|||In SQL Server 2000 replication can solve your problem but I do not know about SQL Server 7.0.

Blindman's idea is easy & will be work for you...|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.I suppose that by the strictest definition that is true, but I tend to think of SSIS (http://msdn.microsoft.com/SQL/bi/integration/default.aspx) as being the descendant of DTS.

-PatP|||Descendent, bastard child, unholy spawn, however you want to call it...|||Descendent, bastard child, unholy spawn, however you want to call it...So, how do you really feel about SSIS? You don't have to hold back, we understand. ;)

-PatP|||select @.Functionality/@.Documentation

----
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Data hiding

Hi,
I wonder if anyone knows if it is possible to configure SQL Server 2000 so
that rows inserted in a table by user A are invisible for user B. User A and
B both insert and select into the same table but only sees their own rows. Is
there inbuild support for such "data hiding" or would I have to build myself?
//Magnus
http://vyaskn.tripod.com/row_level_s..._databases.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> Hi,
> I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> that rows inserted in a table by user A are invisible for user B. User A and
> B both insert and select into the same table but only sees their own rows. Is
> there inbuild support for such "data hiding" or would I have to build myself?
> //Magnus
|||Thanks!
So SQL Server does not directly support VPD, like Oracle apparently does.
What ever solution I choose, I'll be forced to make changes in my client. Is
there a reason to why SQL Server lacks this feature?
/Magnus
"Tibor Karaszi" wrote:

> http://vyaskn.tripod.com/row_level_s..._databases.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
>
>
|||We're doing this now, and no client changes are needed. We removed SELECT
from the underlying tables, and created Views based on suser_sname as
suggested.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Thanks!
> So SQL Server does not directly support VPD, like Oracle apparently does.
> What ever solution I choose, I'll be forced to make changes in my client.
Is[vbcol=seagreen]
> there a reason to why SQL Server lacks this feature?
> /Magnus
> "Tibor Karaszi" wrote:
2000 so[vbcol=seagreen]
A and[vbcol=seagreen]
rows. Is[vbcol=seagreen]
myself?[vbcol=seagreen]
|||Hi Jeff!
Hmm, I don't really understand what you mean with removing SELECT from the
underlying tables. My problem is that my client doesn't do all db-access
through stored procedures. Have you been able to implement row level security
without forcing the client to use stored procedures? Then *please* tell me
how you do it!
/Magnus
"Jeff Dillon" wrote:

> We're doing this now, and no client changes are needed. We removed SELECT
> from the underlying tables, and created Views based on suser_sname as
> suggested.
> Jeff
> "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Is
> 2000 so
> A and
> rows. Is
> myself?
>
>
|||We're not using stored procedures either.You remove SELECT permissions from
the tables, then create Views that use suser_sname() in the Where clause,
and give SELECT permissions on the Views.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:B69BAD33-2BFF-489E-83F7-4BDC88AD66EA@.microsoft.com...
> Hi Jeff!
> Hmm, I don't really understand what you mean with removing SELECT from the
> underlying tables. My problem is that my client doesn't do all db-access
> through stored procedures. Have you been able to implement row level
security[vbcol=seagreen]
> without forcing the client to use stored procedures? Then *please* tell me
> how you do it!
> /Magnus
> "Jeff Dillon" wrote:
SELECT[vbcol=seagreen]
does.[vbcol=seagreen]
client.[vbcol=seagreen]
http://vyaskn.tripod.com/row_level_s..._databases.htm[vbcol=seagreen]
User[vbcol=seagreen]
own[vbcol=seagreen]
build[vbcol=seagreen]

Data hiding

Hi,
I wonder if anyone knows if it is possible to configure SQL Server 2000 so
that rows inserted in a table by user A are invisible for user B. User A and
B both insert and select into the same table but only sees their own rows. Is
there inbuild support for such "data hiding" or would I have to build myself?
//Magnushttp://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> Hi,
> I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> that rows inserted in a table by user A are invisible for user B. User A and
> B both insert and select into the same table but only sees their own rows. Is
> there inbuild support for such "data hiding" or would I have to build myself?
> //Magnus|||Thanks!
So SQL Server does not directly support VPD, like Oracle apparently does.
What ever solution I choose, I'll be forced to make changes in my client. Is
there a reason to why SQL Server lacks this feature?
/Magnus
"Tibor Karaszi" wrote:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > Hi,
> > I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> > that rows inserted in a table by user A are invisible for user B. User A and
> > B both insert and select into the same table but only sees their own rows. Is
> > there inbuild support for such "data hiding" or would I have to build myself?
> >
> > //Magnus
>
>|||We're doing this now, and no client changes are needed. We removed SELECT
from the underlying tables, and created Views based on suser_sname as
suggested.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Thanks!
> So SQL Server does not directly support VPD, like Oracle apparently does.
> What ever solution I choose, I'll be forced to make changes in my client.
Is
> there a reason to why SQL Server lacks this feature?
> /Magnus
> "Tibor Karaszi" wrote:
> > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > Hi,
> > > I wonder if anyone knows if it is possible to configure SQL Server
2000 so
> > > that rows inserted in a table by user A are invisible for user B. User
A and
> > > B both insert and select into the same table but only sees their own
rows. Is
> > > there inbuild support for such "data hiding" or would I have to build
myself?
> > >
> > > //Magnus
> >
> >
> >|||Hi Jeff!
Hmm, I don't really understand what you mean with removing SELECT from the
underlying tables. My problem is that my client doesn't do all db-access
through stored procedures. Have you been able to implement row level security
without forcing the client to use stored procedures? Then *please* tell me
how you do it!
/Magnus
"Jeff Dillon" wrote:
> We're doing this now, and no client changes are needed. We removed SELECT
> from the underlying tables, and created Views based on suser_sname as
> suggested.
> Jeff
> "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > Thanks!
> >
> > So SQL Server does not directly support VPD, like Oracle apparently does.
> > What ever solution I choose, I'll be forced to make changes in my client.
> Is
> > there a reason to why SQL Server lacks this feature?
> >
> > /Magnus
> >
> > "Tibor Karaszi" wrote:
> >
> > > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > Hi,
> > > > I wonder if anyone knows if it is possible to configure SQL Server
> 2000 so
> > > > that rows inserted in a table by user A are invisible for user B. User
> A and
> > > > B both insert and select into the same table but only sees their own
> rows. Is
> > > > there inbuild support for such "data hiding" or would I have to build
> myself?
> > > >
> > > > //Magnus
> > >
> > >
> > >
>
>|||We're not using stored procedures either.You remove SELECT permissions from
the tables, then create Views that use suser_sname() in the Where clause,
and give SELECT permissions on the Views.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:B69BAD33-2BFF-489E-83F7-4BDC88AD66EA@.microsoft.com...
> Hi Jeff!
> Hmm, I don't really understand what you mean with removing SELECT from the
> underlying tables. My problem is that my client doesn't do all db-access
> through stored procedures. Have you been able to implement row level
security
> without forcing the client to use stored procedures? Then *please* tell me
> how you do it!
> /Magnus
> "Jeff Dillon" wrote:
> > We're doing this now, and no client changes are needed. We removed
SELECT
> > from the underlying tables, and created Views based on suser_sname as
> > suggested.
> >
> > Jeff
> > "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> > message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > > Thanks!
> > >
> > > So SQL Server does not directly support VPD, like Oracle apparently
does.
> > > What ever solution I choose, I'll be forced to make changes in my
client.
> > Is
> > > there a reason to why SQL Server lacks this feature?
> > >
> > > /Magnus
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > >
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > > Hi,
> > > > > I wonder if anyone knows if it is possible to configure SQL Server
> > 2000 so
> > > > > that rows inserted in a table by user A are invisible for user B.
User
> > A and
> > > > > B both insert and select into the same table but only sees their
own
> > rows. Is
> > > > > there inbuild support for such "data hiding" or would I have to
build
> > myself?
> > > > >
> > > > > //Magnus
> > > >
> > > >
> > > >
> >
> >
> >