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
Showing posts with label updated. Show all posts
Showing posts with label updated. Show all posts
Thursday, March 29, 2012
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?
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?
Sunday, February 19, 2012
Data in linked server is not updated
I linked server S2 to server S1. When I linked it, the database in S2 had
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? But
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work. I
even deleted the link and relink it again. Still didn't work. Any suggestion?
Thanks in advance.
Lisa
lwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. How
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result? But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work. I
> even deleted the link and relink it again. Still didn't work. Any suggestion?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me..
"Tracy McKibben" wrote:
> lwidjaya wrote:
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? But
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work. I
even deleted the link and relink it again. Still didn't work. Any suggestion?
Thanks in advance.
Lisa
lwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. How
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result? But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work. I
> even deleted the link and relink it again. Still didn't work. Any suggestion?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me..
"Tracy McKibben" wrote:
> lwidjaya wrote:
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Data in linked server is not updated
I linked server S2 to server S1. When I linked it, the database in S2 had
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? Bu
t
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work.
I
even deleted the link and relink it again. Still didn't work. Any suggestion
?
Thanks in advance.
Lisalwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. H
ow
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result?
But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work
. I
> even deleted the link and relink it again. Still didn't work. Any suggesti
on?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me..
"Tracy McKibben" wrote:
> lwidjaya wrote:
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you'
re
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? Bu
t
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work.
I
even deleted the link and relink it again. Still didn't work. Any suggestion
?
Thanks in advance.
Lisalwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. H
ow
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result?
But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work
. I
> even deleted the link and relink it again. Still didn't work. Any suggesti
on?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me..
"Tracy McKibben" wrote:
> lwidjaya wrote:
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you'
re
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Data in linked server is not updated
I linked server S2 to server S1. When I linked it, the database in S2 had
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? But
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work. I
even deleted the link and relink it again. Still didn't work. Any suggestion?
Thanks in advance.
Lisalwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. How
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result? But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work. I
> even deleted the link and relink it again. Still didn't work. Any suggestion?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me.. :)
"Tracy McKibben" wrote:
> lwidjaya wrote:
> > I linked server S2 to server S1. When I linked it, the database in S2 had
> > data up to 11/17/06. Users entered new data to that database since then. How
> > come when I ran a query using the linked database, (using select * from
> > [S2].dbo.dbname.tablename), the new data was not in the query result? But
> > when I ran the query in S2 itself I got the correct data. I refreshed the
> > link by right clicking the server name and hit refresh, but it didn't work. I
> > even deleted the link and relink it again. Still didn't work. Any suggestion?
> > Thanks in advance.
> >
> > Lisa
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
data up to 11/17/06. Users entered new data to that database since then. How
come when I ran a query using the linked database, (using select * from
[S2].dbo.dbname.tablename), the new data was not in the query result? But
when I ran the query in S2 itself I got the correct data. I refreshed the
link by right clicking the server name and hit refresh, but it didn't work. I
even deleted the link and relink it again. Still didn't work. Any suggestion?
Thanks in advance.
Lisalwidjaya wrote:
> I linked server S2 to server S1. When I linked it, the database in S2 had
> data up to 11/17/06. Users entered new data to that database since then. How
> come when I ran a query using the linked database, (using select * from
> [S2].dbo.dbname.tablename), the new data was not in the query result? But
> when I ran the query in S2 itself I got the correct data. I refreshed the
> link by right clicking the server name and hit refresh, but it didn't work. I
> even deleted the link and relink it again. Still didn't work. Any suggestion?
> Thanks in advance.
> Lisa
If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
not looking at the proper table. The proper format would be
"[S2].dbname.dbo.tablename"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
thanks for your reply. It was my fault. We have the new server and a new
testing server. Since they're in different domain than S1, I used their IP
address and made aliases. I used the testing server's IP address for the S2
alias. That's why it wasn't updated. Stupid me.. :)
"Tracy McKibben" wrote:
> lwidjaya wrote:
> > I linked server S2 to server S1. When I linked it, the database in S2 had
> > data up to 11/17/06. Users entered new data to that database since then. How
> > come when I ran a query using the linked database, (using select * from
> > [S2].dbo.dbname.tablename), the new data was not in the query result? But
> > when I ran the query in S2 itself I got the correct data. I refreshed the
> > link by right clicking the server name and hit refresh, but it didn't work. I
> > even deleted the link and relink it again. Still didn't work. Any suggestion?
> > Thanks in advance.
> >
> > Lisa
> If "[S2].dbo.dbname.tablename" is truly the format that you used, you're
> not looking at the proper table. The proper format would be
> "[S2].dbname.dbo.tablename"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Friday, February 17, 2012
Data import
I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.Are you saying that if the process fails, the old files still exist. Do the filenames change - and what about date/time for the files ? Also, what would happen if you deleted or moved the files when you had successfully imported the data ? And what are you using now to import the data ?|||Yes, the files are overwritten nightly. I am using a DTS package to import from the textfile. I thought about just deleting the files after the import, then the jobs would just fail if no files were there, but I just wondered if there was a way for SQL to recognize the date on the file before running the job.|||You can do either - run the dts based on file existence or on file date/time. I would add a file existence step in your dts package and a file move/delete step after the import was successful.|||How would I word the existence step based on date/time? Thanks|||You can create an activex script task using the filesystemobject.|||Have you worked with the filesystemobject - if not I can show you an example.|||Just created it and it seems to be working, but I have it setup only to see if the file exists, if it doesn't then the package fails, which is what I want. Is there a way to have it check for file date and fail if it is not correct instead of just if it exists?|||You can use the File object and read the DateCreated property. You can create the File object using the GetFile method of the filesystemobject.
Subscribe to:
Posts (Atom)