Hi everyone,
Plese help me out. I import one database from one server to another. This process able to copy all the tables and functions but it copied only few stored procedures.
so tell me how to copy or import remaining stored procedures?You could always right click on the database, go toall tasks and hit generate script and select the sps you need scripted and then run said script on the new server.|||Or better still
try using the Copy Database Wizard instead of the Import/Export Data Wizard
:-) GW
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Monday, March 26, 2012
ms sql server enterprise manager
I have a need to create some database and tables on a remote copy of SQL server. (I have been given a password and userid). How do I get a client version of Enterprise Manager? I understand that if I download the 120 day trial of sql server I will get it, but what happens after 120 days? Is there some other free utility for maintaining and creating a database on the hosted SQL Server or do I need to purchase sql 2000 server just to maintain my databases?Someone mentioned to just install the tools from the 120 day package. What will happen to my Enterprise Manager after 120 days??|||Yes it will continue to work after the 120 days, it's the db engine that stops working.
Friday, March 23, 2012
MS SQL Server 2005 Standard Edition
I have the option of buying a new copy of MS SQL Server 2005 Standard Edition for Itanium based systems, however will this also work on the following spec server
2.8Ghz P4
1Ghz Ram
120Gb IDE hard drive
with windows 2003 standar edition operating system
The part number for the stanard edition of SQL is 228-04025
If it helps you can call me on +44 (0) 1285 644091
Thanks
Simon
No, the IA64 version is for Itanium systems, and it looks like you're trying to get it for a 32-bit machine (P4?). Check here for more info:
http://www.microsoft.com/sql/editions/default.mspx
sqlMS SQL Server 2005 SP2 Problems with SSIS packages
I installed SP2 to one of our servers to see what we'd need to do for our Production systems. The server in question is used for a log shipped copy of production, SSRS, SSIS and general duty stuff. I got errors in two areas during the installation -- 1 dealing with SSNS (don't use it yet on that server) and the second being the Client product.
After a reboot I went into a Business Studio solution I've been working on (and using) for several months. None of my packages are working--each having the following problem:
Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'.
This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}'
failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))..
It seems to be in the designer as it is unable to display the the graphical version but it does let me switch to the code view.
What is the easiest way to troubleshoot and/or fix this?
For some reason it didn't register the new DTS.DLL correctly. Running
regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
made the problem go away. Things that make you go 'hmmmmmmmmm'.
I wonder what else is busted...
MS SQL Server 2005 - "Copy Database" from v8 to v9
Hi dear devs, admins or users!
Two weeks ago I had a problem with transfering databases from v8 to v9, I placed the question here (original message attached), but I received not the answer which I need. I try to transfer databases with the CDW ("Copy Database Wizard"), which should run through 5 action steps. The process stops after the 'action 2', the step "Create Package" failed allways. The error message which I receive is "Library not registered". I was very surprised, because two weeks ago the process stoped after the action 4 (4/5) while the step "Execute SQL Server Agent Job". I changed nothing on my system or databases. The only thing I can remember is the "Microsoft Update" downloaded and installed a new patch, but it wasn't a SQL Server 2005 update or something with databases.
I have now two questions. What means the error "Library not registered" while the CDW process and what could be the reason for the error in 'action 5' "Execute SQL Server Agent Job" while the CDW process? **(CDW = "Copy Database Wizard")
[original message from 2006-10-17]
I have following problem with the "SQL Server 2005 Developer Edition". I am in a small network and try to copy a database from a version 8.0 to a version 9.0 "SQL Server" (2 computers). I use the "Copy Database Wizard" and the process did 4 from 5 actions correctly, the last action with the name "Execute SQL Server Agent Job" fails allways. My "SQL Server Agent" process is started and runs well. In real I don't need the "SQL Server Agent" in any way in my database, so I don't understand why this process generates this error. I try to copy this old "Portal" "ASP.NET 1.1" database to my new "SQL Server 2005". Can someone help me?
Thanks
Arzu Bazman · ?US DEV United Systems?
Here is the thread we know SQL Server so we gave you two ways that works, Copy database wizard is one of those wizards it is there but most experienced people don't use it. I think it is failing because it cannot convert TDS(tabular data stream) 80 to 90 2000 to 2005, the problem is you don't need TDS to move a database. Detach and Attach and Backup and Restore wizard don't have the problem.
http://forums.asp.net/thread/1436904.aspx
|||Hi!
Thank you very much for the answer(s). I remember very well that you answered my first answer yet, but I am someone who wants know things very exactly and when something doesn't work, I want allways know the reason. I thought my SQL Server 2005 is not configured well or something is missing, and thats why I asked a second time about the "Copy Database Wizard". Now that you told me that some problems exist with the CDW, I will try the Backup solution for the transfer from v8 to v9 of course. I am sorry, that I didn't that immediately after your first answer (2 weeks ago), but like I said I thought I configured something wrong and wanted know it very exactly.
Thanks
Arzu Bazman · ?US DEV United Systems?
Wednesday, March 21, 2012
ms sql server 2000 too weak ?
It seems the authority for DBA is too much to control the safety of .mdf
, why not add an additional password or key to protect it, if someone
copy the .mdf files and install to a new sql server service, they can
read everything using sa facility, is it worse than ms.access ?
at least ms.access still need some extra job to crack it, but the .mdf
is too simple, just copy and read it.
Especially the MSDE version in one single computer, even the hardware
technician can duplicate and sell your important data.
Anyone have solution for this security problem ?
--
Best regards,
Ridwan
--
PemBukuan.Com
http://www.as3000.comRW,
Security in general is said to start with the physical box - once this is
compromised then there's little you can do (eg Linux can be used to bypass
NTFS so file system security doesn't help). SQL Server security itself is
based on logins, users and permissions/roles, all of which exist in the
database file, so, after the box is accessed (compromised), someone needs to
be able to access/compromise the file.
There's no simple solution apart from securing the box and the files; you
can password protect your backups but not the datafiles.
Regards,
Paul Ibison|||Why not MS add an additional physics login password as an option ? just
like what we have in excel, word, access ? I know that kind of password
is too simple, they can build a more advance password, I think may be
they don't want to take the risk of while users forget the password.
If developer want to distribute an application with safe and small
capacity database, then I think the MSDE is not a choice.
Paul Ibison wrote:
> RW,
> Security in general is said to start with the physical box - once this is
> compromised then there's little you can do (eg Linux can be used to bypass
> NTFS so file system security doesn't help). SQL Server security itself is
> based on logins, users and permissions/roles, all of which exist in the
> database file, so, after the box is accessed (compromised), someone needs to
> be able to access/compromise the file.
> There's no simple solution apart from securing the box and the files; you
> can password protect your backups but not the datafiles.
> Regards,
> Paul Ibison
--
, why not add an additional password or key to protect it, if someone
copy the .mdf files and install to a new sql server service, they can
read everything using sa facility, is it worse than ms.access ?
at least ms.access still need some extra job to crack it, but the .mdf
is too simple, just copy and read it.
Especially the MSDE version in one single computer, even the hardware
technician can duplicate and sell your important data.
Anyone have solution for this security problem ?
--
Best regards,
Ridwan
--
PemBukuan.Com
http://www.as3000.comRW,
Security in general is said to start with the physical box - once this is
compromised then there's little you can do (eg Linux can be used to bypass
NTFS so file system security doesn't help). SQL Server security itself is
based on logins, users and permissions/roles, all of which exist in the
database file, so, after the box is accessed (compromised), someone needs to
be able to access/compromise the file.
There's no simple solution apart from securing the box and the files; you
can password protect your backups but not the datafiles.
Regards,
Paul Ibison|||Why not MS add an additional physics login password as an option ? just
like what we have in excel, word, access ? I know that kind of password
is too simple, they can build a more advance password, I think may be
they don't want to take the risk of while users forget the password.
If developer want to distribute an application with safe and small
capacity database, then I think the MSDE is not a choice.
Paul Ibison wrote:
> RW,
> Security in general is said to start with the physical box - once this is
> compromised then there's little you can do (eg Linux can be used to bypass
> NTFS so file system security doesn't help). SQL Server security itself is
> based on logins, users and permissions/roles, all of which exist in the
> database file, so, after the box is accessed (compromised), someone needs to
> be able to access/compromise the file.
> There's no simple solution apart from securing the box and the files; you
> can password protect your backups but not the datafiles.
> Regards,
> Paul Ibison
--
ms sql server 2000 security too weak ?
It seems the authority for DBA is too much to control the safety of .mdf
, why not add an additional password or key to protect it, if someone
copy the .mdf files and install to a new sql server service, they can
read everything using sa facility, is it worse than ms.access ?
at least ms.access still need some extra job to crack it, but the .mdf
is too simple, just copy and read it.
Especially the MSDE version in one single computer, even the hardware
technician can duplicate and sell your important data.
Anyone have solution for this security problem ?
--
Best regards,
Ridwan
--
PemBukuan.Com
http://www.as3000.com"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
You have a choice. Have MSDE run, on a reserved account
- NTFS security
- Data Encryption
- Also, you can store data on a raw partition, that cannot be copied so
easily.
b.t.w. there is nearly no protection against harddisk access by a
technician. You can't blame MS for that. But data encryption by the
application that uses MSDE is a solution...|||1. using NTFS security still allow to get in, and duplicate the
database, this is not why I mean, but they can copy and open it in
another server without any protection.
2. using data encryption of course will slow down the performance while
we process large amount of data
I am not blaming MS, actually the sql server is quite a good and easy to
maintain database, only we are so curious, why other user data like
excel spreadsheet, word, access can have their own password, and
specially the most important data container (sql server) open like a
mall and welcome in, u just login in as 'sa' and u get everything.
Why not MS add an additional login password as an option, may be that's
much better than let it open.
> You have a choice. Have MSDE run, on a reserved account
> - NTFS security
> - Data Encryption
> - Also, you can store data on a raw partition, that cannot be copied so
> easily.
> b.t.w. there is nearly no protection against harddisk access by a
> technician. You can't blame MS for that. But data encryption by the
> application that uses MSDE is a solution...|||"RW" <goldbase@.centrin.net.id> wrote in message
news:407AB799.4F6F@.centrin.net.id...
> 1. using NTFS security still allow to get in, and duplicate the
> database, this is not why I mean, but they can copy and open it in
> another server without any protection.
> 2. using data encryption of course will slow down the performance while
> we process large amount of data
see below...
> I am not blaming MS, actually the sql server is quite a good and easy to
> maintain database, only we are so curious, why other user data like
> excel spreadsheet, word, access can have their own password, and
> specially the most important data container (sql server) open like a
> mall and welcome in, u just login in as 'sa' and u get everything.
> Why not MS add an additional login password as an option, may be that's
> much better than let it open.
Applying a single password is really a nope-operation. for instance, SQL
stored procs can be encrypted, but they can be decripted using 'tools' that
are available on the net.
So that's why the 'slow' operation, that is a 3 key-algorithm
(public/private/session) is the ONLY viable solution to safegard a file. A
single password with 'xor' encryption on a file is as explained, useless.
Cheers,|||hi
what is necessary to do for encrypt data?
--
atte,
Hernán Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . . .
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> escribió en el mensaje
news:uj3L1IFIEHA.3356@.TK2MSFTNGP11.phx.gbl...
"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
You have a choice. Have MSDE run, on a reserved account
- NTFS security
- Data Encryption
- Also, you can store data on a raw partition, that cannot be copied so
easily.
b.t.w. there is nearly no protection against harddisk access by a
technician. You can't blame MS for that. But data encryption by the
application that uses MSDE is a solution...|||> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> escribió en
el mensaje
> news:uj3L1IFIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:4079FBD2.559B@.centrin.net.id...
> > It seems the authority for DBA is too much to control the safety of .mdf
> > , why not add an additional password or key to protect it, if someone
> > copy the .mdf files and install to a new sql server service, they can
> > read everything using sa facility, is it worse than ms.access ?
> >
> > at least ms.access still need some extra job to crack it, but the .mdf
> > is too simple, just copy and read it.
> >
> > Especially the MSDE version in one single computer, even the hardware
> > technician can duplicate and sell your important data.
> >
> > Anyone have solution for this security problem ?
> You have a choice. Have MSDE run, on a reserved account
> - NTFS security
> - Data Encryption
> - Also, you can store data on a raw partition, that cannot be copied so
> easily.
"Hernán Castelo" <hhh@.hotmail.com> wrote in message
news:%23KshfkMIEHA.3476@.TK2MSFTNGP11.phx.gbl...
> hi
> what is necessary to do for encrypt data?
> --
> atte,
> Hernán Castelo
> UTN Buenos Aires
> . . . . . . . . . . . . . . . . . . . . . . . . .
.
Your application can encrypt data. If you have .NET you can use Rijnhaeve
(If I spell correctly) and such. .NET samples show how to do it.
With C++ (7.0 and higher) there are encryption templates as well.|||You didn't get my question, what I mean is if your database which you
have protect with the algorithm and re-install by somebody in their
server, then all your data will be seen and access using their 'sa'
login, so where's the protection ?
Egbert Nierop (MVP for IIS) wrote:
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:407AB799.4F6F@.centrin.net.id...
> > 1. using NTFS security still allow to get in, and duplicate the
> > database, this is not why I mean, but they can copy and open it in
> > another server without any protection.
> >
> > 2. using data encryption of course will slow down the performance while
> > we process large amount of data
> see below...
> > I am not blaming MS, actually the sql server is quite a good and easy to
> > maintain database, only we are so curious, why other user data like
> > excel spreadsheet, word, access can have their own password, and
> > specially the most important data container (sql server) open like a
> > mall and welcome in, u just login in as 'sa' and u get everything.
> >
> > Why not MS add an additional login password as an option, may be that's
> > much better than let it open.
> Applying a single password is really a nope-operation. for instance, SQL
> stored procs can be encrypted, but they can be decripted using 'tools' that
> are available on the net.
> So that's why the 'slow' operation, that is a 3 key-algorithm
> (public/private/session) is the ONLY viable solution to safegard a file. A
> single password with 'xor' encryption on a file is as explained, useless.
> Cheers,|||If the user is an administrator of the SQL Server, then they can steal your
MDF files. But then, they can do anything anyway.
If the user is an administrator of the Windows machine that SQL Server is
on, then they can steal everything on the server anyway.
Normal users can not do this.
So, you need to trust your administrators.
Anyway, even if there was a "separate" password, how would your applications
access the database? They would need the password, which means it has to be
stored somewhere, which means the administrator could steal it from there
(eg from the client application, or by monitoring the traffic that goes into
SQL Server).
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"RW" <goldbase@.centrin.net.id> wrote in message
news:407B4E73.682C@.centrin.net.id...
: You didn't get my question, what I mean is if your database which you
: have protect with the algorithm and re-install by somebody in their
: server, then all your data will be seen and access using their 'sa'
: login, so where's the protection ?
:
:
:
: Egbert Nierop (MVP for IIS) wrote:
: >
: > "RW" <goldbase@.centrin.net.id> wrote in message
: > news:407AB799.4F6F@.centrin.net.id...
: > > 1. using NTFS security still allow to get in, and duplicate the
: > > database, this is not why I mean, but they can copy and open it in
: > > another server without any protection.
: > >
: > > 2. using data encryption of course will slow down the performance
while
: > > we process large amount of data
: >
: > see below...
: >
: > > I am not blaming MS, actually the sql server is quite a good and easy
to
: > > maintain database, only we are so curious, why other user data like
: > > excel spreadsheet, word, access can have their own password, and
: > > specially the most important data container (sql server) open like a
: > > mall and welcome in, u just login in as 'sa' and u get everything.
: > >
: > > Why not MS add an additional login password as an option, may be
that's
: > > much better than let it open.
: >
: > Applying a single password is really a nope-operation. for instance, SQL
: > stored procs can be encrypted, but they can be decripted using 'tools'
that
: > are available on the net.
: > So that's why the 'slow' operation, that is a 3 key-algorithm
: > (public/private/session) is the ONLY viable solution to safegard a file.
A
: > single password with 'xor' encryption on a file is as explained,
useless.
: >
: > Cheers,
:|||Sometimes trusting people too full is risky to the company, it should be
a double checking procedure and control by two authorized person.
About the monitoring data traffic is not very easy do that if the
application using a native database driver, except ODBC.
My suggestion is when attaching the MDF files will require the original
serial number of ms.sql server 2000 where it was created, I think at
least this is another way to protect the MDF files, even somebody or the
kick out administrator copy it, then it's useless, they should know the
serial number to access the MDF.
What do you think ?
brgs,
Ridwan
Ken Schaefer wrote:
> If the user is an administrator of the SQL Server, then they can steal your
> MDF files. But then, they can do anything anyway.
> If the user is an administrator of the Windows machine that SQL Server is
> on, then they can steal everything on the server anyway.
> Normal users can not do this.
> So, you need to trust your administrators.
> Anyway, even if there was a "separate" password, how would your applications
> access the database? They would need the password, which means it has to be
> stored somewhere, which means the administrator could steal it from there
> (eg from the client application, or by monitoring the traffic that goes into
> SQL Server).
> Cheers
> Ken
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:407B4E73.682C@.centrin.net.id...
> : You didn't get my question, what I mean is if your database which you
> : have protect with the algorithm and re-install by somebody in their
> : server, then all your data will be seen and access using their 'sa'
> : login, so where's the protection ?
> :
> :
> :
> : Egbert Nierop (MVP for IIS) wrote:
> : >
> : > "RW" <goldbase@.centrin.net.id> wrote in message
> : > news:407AB799.4F6F@.centrin.net.id...
> : > > 1. using NTFS security still allow to get in, and duplicate the
> : > > database, this is not why I mean, but they can copy and open it in
> : > > another server without any protection.
> : > >
> : > > 2. using data encryption of course will slow down the performance
> while
> : > > we process large amount of data
> : >
> : > see below...
> : >
> : > > I am not blaming MS, actually the sql server is quite a good and easy
> to
> : > > maintain database, only we are so curious, why other user data like
> : > > excel spreadsheet, word, access can have their own password, and
> : > > specially the most important data container (sql server) open like a
> : > > mall and welcome in, u just login in as 'sa' and u get everything.
> : > >
> : > > Why not MS add an additional login password as an option, may be
> that's
> : > > much better than let it open.
> : >
> : > Applying a single password is really a nope-operation. for instance, SQL
> : > stored procs can be encrypted, but they can be decripted using 'tools'
> that
> : > are available on the net.
> : > So that's why the 'slow' operation, that is a 3 key-algorithm
> : > (public/private/session) is the ONLY viable solution to safegard a file.
> A
> : > single password with 'xor' encryption on a file is as explained,
> useless.
> : >
> : > Cheers,
> :|||> what is necessary to do for encrypt data?
>
Checkout www.database-encryption.com
www.sql-shield.com|||Hi
You are quite right Ridwan. MSSQL is exceptionally weak when it comes to
this. You have hit the nail right on the head. We are quite astonished
that MS has not taken data security seriously. They could at least have
provided some sort of encryption technique that could have restricted access
to all objects in the database outside of SA or sysadmin. The basic
underlying structure of their SQL engine is at fault here. The SA login is
a disaster as is the total control given to sysadmin. It basically makes
the product quite unusable in a mission critical environment. Nothing more,
nothing less. If you do not turn to third party tools to help you with this
dilemna you are basically stuck. If you take data security seriously you
are snookered. You may have to look for a more serious DBMS. We are
currently looking at third party options but most of them do not lock down
table structures and relationships. Hoping to find something that will lock
down the entire database so that it is NOT accessible on another server by
some individual that has gaily made his/her self system administrator. A
shocking state of affairs.
Cheers
Andre
"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
> --
> Best regards,
>
> Ridwan
> --
> PemBukuan.Com
> http://www.as3000.com
>
, why not add an additional password or key to protect it, if someone
copy the .mdf files and install to a new sql server service, they can
read everything using sa facility, is it worse than ms.access ?
at least ms.access still need some extra job to crack it, but the .mdf
is too simple, just copy and read it.
Especially the MSDE version in one single computer, even the hardware
technician can duplicate and sell your important data.
Anyone have solution for this security problem ?
--
Best regards,
Ridwan
--
PemBukuan.Com
http://www.as3000.com"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
You have a choice. Have MSDE run, on a reserved account
- NTFS security
- Data Encryption
- Also, you can store data on a raw partition, that cannot be copied so
easily.
b.t.w. there is nearly no protection against harddisk access by a
technician. You can't blame MS for that. But data encryption by the
application that uses MSDE is a solution...|||1. using NTFS security still allow to get in, and duplicate the
database, this is not why I mean, but they can copy and open it in
another server without any protection.
2. using data encryption of course will slow down the performance while
we process large amount of data
I am not blaming MS, actually the sql server is quite a good and easy to
maintain database, only we are so curious, why other user data like
excel spreadsheet, word, access can have their own password, and
specially the most important data container (sql server) open like a
mall and welcome in, u just login in as 'sa' and u get everything.
Why not MS add an additional login password as an option, may be that's
much better than let it open.
> You have a choice. Have MSDE run, on a reserved account
> - NTFS security
> - Data Encryption
> - Also, you can store data on a raw partition, that cannot be copied so
> easily.
> b.t.w. there is nearly no protection against harddisk access by a
> technician. You can't blame MS for that. But data encryption by the
> application that uses MSDE is a solution...|||"RW" <goldbase@.centrin.net.id> wrote in message
news:407AB799.4F6F@.centrin.net.id...
> 1. using NTFS security still allow to get in, and duplicate the
> database, this is not why I mean, but they can copy and open it in
> another server without any protection.
> 2. using data encryption of course will slow down the performance while
> we process large amount of data
see below...
> I am not blaming MS, actually the sql server is quite a good and easy to
> maintain database, only we are so curious, why other user data like
> excel spreadsheet, word, access can have their own password, and
> specially the most important data container (sql server) open like a
> mall and welcome in, u just login in as 'sa' and u get everything.
> Why not MS add an additional login password as an option, may be that's
> much better than let it open.
Applying a single password is really a nope-operation. for instance, SQL
stored procs can be encrypted, but they can be decripted using 'tools' that
are available on the net.
So that's why the 'slow' operation, that is a 3 key-algorithm
(public/private/session) is the ONLY viable solution to safegard a file. A
single password with 'xor' encryption on a file is as explained, useless.
Cheers,|||hi
what is necessary to do for encrypt data?
--
atte,
Hernán Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . . .
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> escribió en el mensaje
news:uj3L1IFIEHA.3356@.TK2MSFTNGP11.phx.gbl...
"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
You have a choice. Have MSDE run, on a reserved account
- NTFS security
- Data Encryption
- Also, you can store data on a raw partition, that cannot be copied so
easily.
b.t.w. there is nearly no protection against harddisk access by a
technician. You can't blame MS for that. But data encryption by the
application that uses MSDE is a solution...|||> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> escribió en
el mensaje
> news:uj3L1IFIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:4079FBD2.559B@.centrin.net.id...
> > It seems the authority for DBA is too much to control the safety of .mdf
> > , why not add an additional password or key to protect it, if someone
> > copy the .mdf files and install to a new sql server service, they can
> > read everything using sa facility, is it worse than ms.access ?
> >
> > at least ms.access still need some extra job to crack it, but the .mdf
> > is too simple, just copy and read it.
> >
> > Especially the MSDE version in one single computer, even the hardware
> > technician can duplicate and sell your important data.
> >
> > Anyone have solution for this security problem ?
> You have a choice. Have MSDE run, on a reserved account
> - NTFS security
> - Data Encryption
> - Also, you can store data on a raw partition, that cannot be copied so
> easily.
"Hernán Castelo" <hhh@.hotmail.com> wrote in message
news:%23KshfkMIEHA.3476@.TK2MSFTNGP11.phx.gbl...
> hi
> what is necessary to do for encrypt data?
> --
> atte,
> Hernán Castelo
> UTN Buenos Aires
> . . . . . . . . . . . . . . . . . . . . . . . . .
.
Your application can encrypt data. If you have .NET you can use Rijnhaeve
(If I spell correctly) and such. .NET samples show how to do it.
With C++ (7.0 and higher) there are encryption templates as well.|||You didn't get my question, what I mean is if your database which you
have protect with the algorithm and re-install by somebody in their
server, then all your data will be seen and access using their 'sa'
login, so where's the protection ?
Egbert Nierop (MVP for IIS) wrote:
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:407AB799.4F6F@.centrin.net.id...
> > 1. using NTFS security still allow to get in, and duplicate the
> > database, this is not why I mean, but they can copy and open it in
> > another server without any protection.
> >
> > 2. using data encryption of course will slow down the performance while
> > we process large amount of data
> see below...
> > I am not blaming MS, actually the sql server is quite a good and easy to
> > maintain database, only we are so curious, why other user data like
> > excel spreadsheet, word, access can have their own password, and
> > specially the most important data container (sql server) open like a
> > mall and welcome in, u just login in as 'sa' and u get everything.
> >
> > Why not MS add an additional login password as an option, may be that's
> > much better than let it open.
> Applying a single password is really a nope-operation. for instance, SQL
> stored procs can be encrypted, but they can be decripted using 'tools' that
> are available on the net.
> So that's why the 'slow' operation, that is a 3 key-algorithm
> (public/private/session) is the ONLY viable solution to safegard a file. A
> single password with 'xor' encryption on a file is as explained, useless.
> Cheers,|||If the user is an administrator of the SQL Server, then they can steal your
MDF files. But then, they can do anything anyway.
If the user is an administrator of the Windows machine that SQL Server is
on, then they can steal everything on the server anyway.
Normal users can not do this.
So, you need to trust your administrators.
Anyway, even if there was a "separate" password, how would your applications
access the database? They would need the password, which means it has to be
stored somewhere, which means the administrator could steal it from there
(eg from the client application, or by monitoring the traffic that goes into
SQL Server).
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"RW" <goldbase@.centrin.net.id> wrote in message
news:407B4E73.682C@.centrin.net.id...
: You didn't get my question, what I mean is if your database which you
: have protect with the algorithm and re-install by somebody in their
: server, then all your data will be seen and access using their 'sa'
: login, so where's the protection ?
:
:
:
: Egbert Nierop (MVP for IIS) wrote:
: >
: > "RW" <goldbase@.centrin.net.id> wrote in message
: > news:407AB799.4F6F@.centrin.net.id...
: > > 1. using NTFS security still allow to get in, and duplicate the
: > > database, this is not why I mean, but they can copy and open it in
: > > another server without any protection.
: > >
: > > 2. using data encryption of course will slow down the performance
while
: > > we process large amount of data
: >
: > see below...
: >
: > > I am not blaming MS, actually the sql server is quite a good and easy
to
: > > maintain database, only we are so curious, why other user data like
: > > excel spreadsheet, word, access can have their own password, and
: > > specially the most important data container (sql server) open like a
: > > mall and welcome in, u just login in as 'sa' and u get everything.
: > >
: > > Why not MS add an additional login password as an option, may be
that's
: > > much better than let it open.
: >
: > Applying a single password is really a nope-operation. for instance, SQL
: > stored procs can be encrypted, but they can be decripted using 'tools'
that
: > are available on the net.
: > So that's why the 'slow' operation, that is a 3 key-algorithm
: > (public/private/session) is the ONLY viable solution to safegard a file.
A
: > single password with 'xor' encryption on a file is as explained,
useless.
: >
: > Cheers,
:|||Sometimes trusting people too full is risky to the company, it should be
a double checking procedure and control by two authorized person.
About the monitoring data traffic is not very easy do that if the
application using a native database driver, except ODBC.
My suggestion is when attaching the MDF files will require the original
serial number of ms.sql server 2000 where it was created, I think at
least this is another way to protect the MDF files, even somebody or the
kick out administrator copy it, then it's useless, they should know the
serial number to access the MDF.
What do you think ?
brgs,
Ridwan
Ken Schaefer wrote:
> If the user is an administrator of the SQL Server, then they can steal your
> MDF files. But then, they can do anything anyway.
> If the user is an administrator of the Windows machine that SQL Server is
> on, then they can steal everything on the server anyway.
> Normal users can not do this.
> So, you need to trust your administrators.
> Anyway, even if there was a "separate" password, how would your applications
> access the database? They would need the password, which means it has to be
> stored somewhere, which means the administrator could steal it from there
> (eg from the client application, or by monitoring the traffic that goes into
> SQL Server).
> Cheers
> Ken
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> "RW" <goldbase@.centrin.net.id> wrote in message
> news:407B4E73.682C@.centrin.net.id...
> : You didn't get my question, what I mean is if your database which you
> : have protect with the algorithm and re-install by somebody in their
> : server, then all your data will be seen and access using their 'sa'
> : login, so where's the protection ?
> :
> :
> :
> : Egbert Nierop (MVP for IIS) wrote:
> : >
> : > "RW" <goldbase@.centrin.net.id> wrote in message
> : > news:407AB799.4F6F@.centrin.net.id...
> : > > 1. using NTFS security still allow to get in, and duplicate the
> : > > database, this is not why I mean, but they can copy and open it in
> : > > another server without any protection.
> : > >
> : > > 2. using data encryption of course will slow down the performance
> while
> : > > we process large amount of data
> : >
> : > see below...
> : >
> : > > I am not blaming MS, actually the sql server is quite a good and easy
> to
> : > > maintain database, only we are so curious, why other user data like
> : > > excel spreadsheet, word, access can have their own password, and
> : > > specially the most important data container (sql server) open like a
> : > > mall and welcome in, u just login in as 'sa' and u get everything.
> : > >
> : > > Why not MS add an additional login password as an option, may be
> that's
> : > > much better than let it open.
> : >
> : > Applying a single password is really a nope-operation. for instance, SQL
> : > stored procs can be encrypted, but they can be decripted using 'tools'
> that
> : > are available on the net.
> : > So that's why the 'slow' operation, that is a 3 key-algorithm
> : > (public/private/session) is the ONLY viable solution to safegard a file.
> A
> : > single password with 'xor' encryption on a file is as explained,
> useless.
> : >
> : > Cheers,
> :|||> what is necessary to do for encrypt data?
>
Checkout www.database-encryption.com
www.sql-shield.com|||Hi
You are quite right Ridwan. MSSQL is exceptionally weak when it comes to
this. You have hit the nail right on the head. We are quite astonished
that MS has not taken data security seriously. They could at least have
provided some sort of encryption technique that could have restricted access
to all objects in the database outside of SA or sysadmin. The basic
underlying structure of their SQL engine is at fault here. The SA login is
a disaster as is the total control given to sysadmin. It basically makes
the product quite unusable in a mission critical environment. Nothing more,
nothing less. If you do not turn to third party tools to help you with this
dilemna you are basically stuck. If you take data security seriously you
are snookered. You may have to look for a more serious DBMS. We are
currently looking at third party options but most of them do not lock down
table structures and relationships. Hoping to find something that will lock
down the entire database so that it is NOT accessible on another server by
some individual that has gaily made his/her self system administrator. A
shocking state of affairs.
Cheers
Andre
"RW" <goldbase@.centrin.net.id> wrote in message
news:4079FBD2.559B@.centrin.net.id...
> It seems the authority for DBA is too much to control the safety of .mdf
> , why not add an additional password or key to protect it, if someone
> copy the .mdf files and install to a new sql server service, they can
> read everything using sa facility, is it worse than ms.access ?
> at least ms.access still need some extra job to crack it, but the .mdf
> is too simple, just copy and read it.
> Especially the MSDE version in one single computer, even the hardware
> technician can duplicate and sell your important data.
> Anyone have solution for this security problem ?
> --
> Best regards,
>
> Ridwan
> --
> PemBukuan.Com
> http://www.as3000.com
>
Friday, March 9, 2012
MS SQL copy new and modified rows from TABLE1 to TABLE2
Hello,
The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
Yes, they are on the same server and in the same Database.|||Yas (yasar1@.gmail.com) writes:
Since the tables are in the same database, triggers is definitely the
way to go.
>
>
>
Since the tables are in the same database, triggers is definitely the
way to go.
>
Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?
>
>Thanks. This is what I'm trying to do now... do you know how I can
>refer to the row that has just been added or modified?
Read up on the INSERTED and DELETED virtual tables that are available
to triggers.
>
>
Read up on the INSERTED and DELETED virtual tables that are available
to triggers.
Hi I'm trying the following for INSERT trigger attached to Table1 but
it doesn't seem to work in that it doesn't insert the new rows into
Table2 from Table1
Well, the code you posted is a trigger on Table2, so...
>
>
>
Well, the code you posted is a trigger on Table2, so...
>
DOH!!! what a silly mistake. :-) do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>
>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
>
Thanks for all your advise and help! by the way do you if there is a
way to edit/change a Trigger once it has been created in MS SQL?
>>
>It's often that when you work with something you are not really confident
>that you look for the difficult mistakes and overlook the simple typos.
>>
>>
>Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
>as I think that expresses more clearly what is going on. But that's a matter
>of taste.
>>
>
>Thanks for all your advise and help! by the way do you if there is a
>way to edit/change a Trigger once it has been created in MS SQL?
Hi Yas,
>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>
>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
Thanks. Just out of curiosity how would you modify the above to use
NOT EXISTS ?
>
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
>
>
Yes, they are on the same server and in the same Database.
Couldn't you just use one table and add column use as a DELETED flag
to logically delete a user so the physical row is still there?|||On Tue, 21 Aug 2007 01:29:46 -0700, Yas <yasar1@.gmail.comwrote:
INSERT INTO Table2
(STATUS,attribute15,email,
lastname1,lastname2,name,
company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email,
b.lastname1, b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b
WHERE NOT EXISTS
(SELECT * FROM Table2 a
WHERE b.Attribute15 = a.Attribute15)
I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?
Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.
Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?
Thanks for any help or advise :-)
YasYas (yasar1@.gmail.com) writes:
Quote:
Originally Posted by
I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?
>
Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.
>
>
Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?
The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?
If the tables are on the same server, a trigger would be the best way
to do it.
If tbe tables are on different server, triggers are still possible, but
if the remote server is unavailable, this would cause the operation on
the source table to fail. In this case, replication may be a way to go.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Aug, 13:46, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
Quote:
Originally Posted by
If the tables are on the same server, a trigger would be the best way
to do it.
Yes, they are on the same server and in the same Database.|||Yas (yasar1@.gmail.com) writes:
Quote:
Originally Posted by
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
Since the tables are in the same database, triggers is definitely the
way to go.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Aug, 15:16, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Yas (yas...@.gmail.com) writes:
Quote:
Originally Posted by
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
Quote:
Originally Posted by
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
Quote:
Originally Posted by
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
>
Since the tables are in the same database, triggers is definitely the
way to go.
>
Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?
In Table1 I have...
CREATE TRIGGER (tr_updateMaster) ON dbo.Table2
FOR INSERT, UPDATE
AS
Here I would like to put something like...
Insert into Table2 new row + 2 extra columns (status and date)
AND/OR
Update modified row in dbo.Table2 with different values in Table1
Thanks again :-)|||On Thu, 16 Aug 2007 08:13:47 -0700, Yas <yasar1@.gmail.comwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>Since the tables are in the same database, triggers is definitely the
>way to go.
>>
>
>Thanks. This is what I'm trying to do now... do you know how I can
>refer to the row that has just been added or modified?
Read up on the INSERTED and DELETED virtual tables that are available
to triggers.
Roy Harvey
Beacon Falls, CT|||On 16 Aug, 17:48, Roy Harvey <roy_har...@.snet.netwrote:
Quote:
Originally Posted by
On Thu, 16 Aug 2007 08:13:47 -0700, Yas <yas...@.gmail.comwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Since the tables are in the same database, triggers is definitely the
way to go.
>
Quote:
Originally Posted by
Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?
>
Read up on the INSERTED and DELETED virtual tables that are available
to triggers.
Hi I'm trying the following for INSERT trigger attached to Table1 but
it doesn't seem to work in that it doesn't insert the new rows into
Table2 from Table1
CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS
INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO
The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.
The idea here is basically when a new row is inserted in Table1, the
above insert command is run and the new row copied over to Table2
Any help?
Thanks in advance|||Yas (yasar1@.gmail.com) writes:
Quote:
Originally Posted by
CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS
>
INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO
>
The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.
Well, the code you posted is a trigger on Table2, so...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Aug, 22:58, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Yas (yas...@.gmail.com) writes:
Quote:
Originally Posted by
CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS
>
Quote:
Originally Posted by
INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO
>
Quote:
Originally Posted by
The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.
>
Well, the code you posted is a trigger on Table2, so...
>
DOH!!! what a silly mistake. :-) do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
Thanks agian|||Yas (yasar1@.gmail.com) writes:
Quote:
Originally Posted by
DOH!!! what a silly mistake. :-)
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
Quote:
Originally Posted by
do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Aug, 08:11, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Yas(yas...@.gmail.com) writes:
Quote:
Originally Posted by
DOH!!! what a silly mistake. :-)
>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>
Quote:
Originally Posted by
do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
>
Thanks for all your advise and help! by the way do you if there is a
way to edit/change a Trigger once it has been created in MS SQL?
Yas|||On Fri, 17 Aug 2007 19:47:32 -0700, Yas wrote:
Quote:
Originally Posted by
>On 17 Aug, 08:11, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
>Yas(yas...@.gmail.com) writes:
Quote:
Originally Posted by
DOH!!! what a silly mistake. :-)
>>
>It's often that when you work with something you are not really confident
>that you look for the difficult mistakes and overlook the simple typos.
>>
Quote:
Originally Posted by
do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
>>
>Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
>as I think that expresses more clearly what is going on. But that's a matter
>of taste.
>>
>
>Thanks for all your advise and help! by the way do you if there is a
>way to edit/change a Trigger once it has been created in MS SQL?
Hi Yas,
Yes. Simply use ALTER TRIGGER instead of CREATE TRIGGER.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 17 Aug, 09:11, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Yas(yas...@.gmail.com) writes:
Quote:
Originally Posted by
DOH!!! what a silly mistake. :-)
>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>
Quote:
Originally Posted by
do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?
>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
Thanks. Just out of curiosity how would you modify the above to use
NOT EXISTS ?
cheers
Yas|||On Aug 16, 2:14 pm, Yas <yas...@.gmail.comwrote:
Quote:
Originally Posted by
On 16 Aug, 13:46, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
Quote:
Originally Posted by
The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?
>
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
>
Quote:
Originally Posted by
If the tables are on the same server, a trigger would be the best way
to do it.
>
Yes, they are on the same server and in the same Database.
Couldn't you just use one table and add column use as a DELETED flag
to logically delete a user so the physical row is still there?|||On Tue, 21 Aug 2007 01:29:46 -0700, Yas <yasar1@.gmail.comwrote:
Quote:
Originally Posted by
INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
Quote:
Originally Posted by
>Just out of curiosity how would you modify the above to use
>NOT EXISTS ?
INSERT INTO Table2
(STATUS,attribute15,email,
lastname1,lastname2,name,
company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email,
b.lastname1, b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b
WHERE NOT EXISTS
(SELECT * FROM Table2 a
WHERE b.Attribute15 = a.Attribute15)
Roy Harvey
Beacon Falls, CT
Subscribe to:
Posts (Atom)