remove cascade update and delete from table

hello, once upon a time when i created my db (originally in access then used the conversion tool, which i now know is wrong!) i thought it would be an amazing idea to have cascading updates and deletes, however it turns out now this is exactly not what i want! if i leave them in then it throws errors when i delete records out of my stock table as related records are in the order_line table here is the code (well i think so, im not the best at sqlserver as you probably can tell already) that im using if anyone can help or point me in the right direction that would be great, thanks

USE [nashdfDB1]GO/****** Object: Table [dbo].[tbl_stock] Script Date: 07/13/2007 02:52:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_stock]( [Stock_ID] [int] IDENTITY(1,1) NOT NULL, [cat_id] [int] NOT NULL CONSTRAINT [DF__tbl_stock__cat_i__15502E78] DEFAULT ((0)), [sub_cat_id] [int] NULL CONSTRAINT [DF__tbl_stock__sub_c__164452B1] DEFAULT ((0)), [location] [int] NULL CONSTRAINT [DF__tbl_stock__locat__173876EA] DEFAULT ((0)), [n_or_sh] [varchar](50) NULL, [title] [varchar](255) NULL, [description] [varchar](255) NULL, [size] [varchar](50) NULL, [colour] [varchar](50) NULL, [cost_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__cost___182C9B23] DEFAULT ((0)), [selling_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__selli__1920BF5C] DEFAULT ((0)), [qty] [varchar](50) NULL, [date] [datetime] NULL CONSTRAINT [DF__tbl_stock__date__1A14E395] DEFAULT (getdate()), [condition] [varchar](255) NULL, [notes] [varchar](255) NULL, [visible] [bit] NULL CONSTRAINT [DF__tbl_stock__visib__1B0907CE] DEFAULT ((1)), [picture1] [varchar](50) NULL, [picture1_thumb] [varchar](50) NULL, [picture2] [varchar](50) NULL, [picture2_thumb] [varchar](50) NULL, [picture3] [varchar](50) NULL, [picture3_thumb] [varchar](50) NULL, [picture4] [varchar](50) NULL, [picture4_thumb] [varchar](50) NULL, [display_price] [varchar](50) NULL, [created_by] [varchar](50) NULL, [buying_in_recipt] [varchar](255) NULL, CONSTRAINT [tbl_stock$PrimaryKey] PRIMARY KEY CLUSTERED( [Stock_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

SET ANSI_PADDING OFF

Regards

Jez

It's hard to read all that DDL but here's the syntax:

ALTER TABLE <table_name>
DROP FOREIGN KEY <foreignkey_name>

See eghttp://www.infogoal.com/sql/sql-drop-foreignkey.htm

You can read more about constraints athttp://www.mssqlcity.com/Articles/General/using_constraints.htm and you can get the full alter table syntax athttp://msdn2.microsoft.com/en-us/library/aa275462(SQL.80).aspx

Remotely update database

Hi,
We have a SQL Server 2000 database on an Win2k server. I'd like to
offer the facility for our customers to add records to our database
from their programs. They just want to make one function call to
create a new record on our database.
This call would return a success or failure code number.
They are not (currently) connected to our system. I had felt that a
VPN would be the best method, but am open to alternative suggestions.
Their programs run on about 100 PC's on their lan, so I figured I'd
need to write a DLL for their server which is constantly connected to
our server, then their function call would invoke a function within
this DLL. For this I'd be using VB6 (sorry, not upgraded to .NET yet).
The idea being that we would then only have one connection to our
server rather than 100.
Security is a key concern - I don't really want to provide their
programmers with the logins to our system, just a compiled DLL that
does it for them.
Do you think this is a workable plan? What would be better?
TIA
PhilHow about creating a Web Service that their programs can access. Then you
don't need a VPN at all.
Andrew J. Kelly SQL MVP
"RS200Phil" <philsowden@.dataservicesltd.co.uk> wrote in message
news:1143819451.670277.13120@.e56g2000cwe.googlegroups.com...
> Hi,
> We have a SQL Server 2000 database on an Win2k server. I'd like to
> offer the facility for our customers to add records to our database
> from their programs. They just want to make one function call to
> create a new record on our database.
> This call would return a success or failure code number.
> They are not (currently) connected to our system. I had felt that a
> VPN would be the best method, but am open to alternative suggestions.
> Their programs run on about 100 PC's on their lan, so I figured I'd
> need to write a DLL for their server which is constantly connected to
> our server, then their function call would invoke a function within
> this DLL. For this I'd be using VB6 (sorry, not upgraded to .NET yet).
> The idea being that we would then only have one connection to our
> server rather than 100.
> Security is a key concern - I don't really want to provide their
> programmers with the logins to our system, just a compiled DLL that
> does it for them.
> Do you think this is a workable plan? What would be better?
> TIA
> Phil
>|||Sounds like a brilliant solution, thanks, Andrew. Please excuse my
ignorance - where would I look for information on how to develop and
implement a web service?
I'm not afraid to get my hands dirty or read manuals. I develop
primarily in ASP and VB.
Cheers
Phil|||I think you would want .Net for this, in which case the tools do 90 percent
of the codign for you. All you need to do is write a single function that
takes parameters and .Net will code the interface. Then you could write a
DLL that would access the web service (again, .net does all the work) which
could run from their desktops or a webserver in their domain. You can do it
without .Net, it is just a lot more work.
However, they still need to be able to access your web server, on your
network, and you still need to make sure that random people on the internet
cannot access your server. So you need to have some security built in to
control access.
"RS200Phil" <philsowden@.dataservicesltd.co.uk> wrote in message
news:1143840697.760335.245570@.t31g2000cwb.googlegroups.com...
> Sounds like a brilliant solution, thanks, Andrew. Please excuse my
> ignorance - where would I look for information on how to develop and
> implement a web service?
> I'm not afraid to get my hands dirty or read manuals. I develop
> primarily in ASP and VB.
> Cheers
> Phil
>|||http://www.google.com/search?hl=en&...vice+sql+server
Andrew J. Kelly SQL MVP
"RS200Phil" <philsowden@.dataservicesltd.co.uk> wrote in message
news:1143840697.760335.245570@.t31g2000cwb.googlegroups.com...
> Sounds like a brilliant solution, thanks, Andrew. Please excuse my
> ignorance - where would I look for information on how to develop and
> implement a web service?
> I'm not afraid to get my hands dirty or read manuals. I develop
> primarily in ASP and VB.
> Cheers
> Phil
>

remoted linked server problem

I am running SQL 2005 trigger and update the table in remote linked server. This is working perfectly when the firewall is off. However when I turn on the firewall, it wont run any more. I need the firewall on for the security issue. How can I run this trigger with firewall on? Thanks.Do not create triggers that operate outside the scope of their own database.|||Do not create triggers that operate outside the scope of their own database.

+1

hmscott

Remote Update takes forever

I have a SQL 2005 & SQL 2000 server. I am attempting to execute a simple update statement, something that looks like:

update AD

set AD.SomeDate = getdate()

from [ServerX].DB.dbo.Table

where ColumnX = 'X'

ServerX is the SQL 2000 box.

ServerY is the SQL 2005 box. Server Y is where this statement is invoked from. (Not shown in statement).

I have a linked server set up.

When executed from the 2000 box, it runs in < 1 second.

When both environments are 2005 to 2005, it takes less than < 1 second.

Try using OPENQUERY to perform the update - I've provided an (untested) example below.

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

Chris

Code Snippet

--Note: Untested so you may need to play around with the syntax. Execute on Server Y.
UPDATE OPENQUERY(ServerX, 'SELECT SomeDate FROM DB.dbo.Table WHERE ColumnX = ''X''')
SET SomeDate = GETDATE()

remote triggers on update

I don't think this is a problem, but I am curious as to why this is happening.

I have two machines, lets call them machine A and machine B.

On machine A I have a job that runs. It has 2 steps. The first step runs replication (no problem). The 2nd step (on success of step 1) updates a row in a table via a linked server on Machine B.

So, step 2 is basically:
update b.dbname.dbo.tablename set success=1 where id=1

On machine B, the table that is updated has an update trigger on it that checks the success flag. If the success flag is 1 then it runs a stored procedure that does a bunch of stuff on machine B (this stuff takes about 10 minutes).

This all works great, however if I check the job history on Machine A, it says that step 2 (the update) takes 10 minutes.

Why is this? Shouldn't the update statement be instantaneous? It seems like the update waits for the trigger to fire before returning. Is that correct?

Like I said, its not a problem, but I was just curious as to why the job on machine A waits for the trigger on machine B to complete.

Hope this makes sense. Thanks

JeffThe trigger is part of the update, and the transaction is not complete until the trigger completes.

blindman

Remote Server

Hi all,

I am trying to locate some scripts for create/update and a nightly scripts that run to update some databases.

Now within the server itself there are no scripts that are running beside the normal system stored procedure.

I notice that the server is a remote server is it possible for the scripts that are running coming from another server.

Once I click on the server (NTBVM) and right click on the property RPC is check, and 'map remote logins to differents local logins' has the radio dial marked however when you look at the list below no information is recorded.

Please help

Thanking you in advanceGreetings, M'Lady! It's good to see you again.

While it is unlikely, it is possible that the scripts would run on another server.

Is this something you are investigating because it is happening now, or something you are planning so it will happen soon?

-PatP|||It something that I was asked to fix and give a time line.

One of the task is to modify registry settings for a OSI servlet(What is the world).

The network admin said that there are scripts that run and I've look everywhere for the scripts and can't find them.

The frontend of this tool call OSI and the backend is SQL. I wonder if I would be able to find the scripts in the tool itself.

I don't have a clue.

Thanks for responding so fast.

Lystra|||Ok, it is pretty easy to define OSI (http://searchnetworking.techtarget.com/sDefinition/0,,sid7_gci212725,00.html), and servlet (http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212966,00.html), although it is somewhat peculiar to see them used together that way.

One thing that might help you is to run the SQL profiler overnight to see who/what connects to your server. That will at least give you a few "breadcrumbs" to start from!

Another "high probability" place to check is the SQL Agent running on the SQL Server. It is quite easy to schedule scripts (and many other things) to run as needed against your SQL server or other machines.

-PatP|||I have check the sql server agent nothing.

I will try profiler overnight.

Since servlet--is a small program that runs on a server, maybe the scripts are running from there?

Thanks

Lystra|||If there was nothing in the SQL Agent, the servlet would be my next guess. Do any of the network/admin/IT types have any kind of documentation for this system whatsoever? It sounds to me like you are the new kid on the block, being sent out to clean up a runaway freight train that has been careening unattended through the office for quite some time!

-PatP|||Yup, you can't be more right. This is my three week at this job. I could tell you the horror stories from the first week.

But I have to admit I love this job because it is pushing me to be a better dba. My last job was a contractor for the government and I was not able to use my troubleshooting skills to the fullest. Here I have to.

Lystra|||A friend of mine is in a similar situation in the DC area. She's been developing MS-Access for one of the government's largest construction contractors for an age. She's reached the point where she wants to go further than they are willing/able to manage, and she's trying to figure out what her next step should be...

Unfortunately, I've been out of the DC area for too long, so I've really lost touch with the IT/business community there. Oh well, can't win them all!

-PatP|||It's pretty much the same as you have left it.

Nothing have change.

Lystra

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:
> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>> I am running SQL 2005 trigger and update the table in remote linked
>> server.
>> This is working perfectly when the firewall is off. However when I turn
>> on
>> the firewall, it won't run any more. I need the firewall on for the
>> security
>> issue. How can I run this trigger with firewall on? Thanks.
>>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:
> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John|||Hi,
I'm just trying to update a table in a remote linked server from a trigger.
The problem is that when the trigger is fired, it gives an error: "No
transaction is active".
I'm using SQL Express 2005 for both servers. The firewall is off in both
machines, also, the MSDTS service is running.
Could you tell me if I'm missing something?
Thanks,
" 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am running SQL 2005 trigger and update the table in remote linked server.
>This is working perfectly when the firewall is off. However when I turn on
>the firewall, it won't run any more. I need the firewall on for the
>security issue. How can I run this trigger with firewall on? Thanks.
>|||Hi
Check that MSDTC is correctly configured
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
Also make sure that the service accounts for SQL Server is a domain account
http://msdn2.microsoft.com/en-us/library/ms143504.aspx#Use_startup_accounts
If you explicitly start a DISTRIBUTED transaction before the statement that
fires the trigger do you still get the error?
John
"Anahi Ludueña" wrote:
> Hi,
> I'm just trying to update a table in a remote linked server from a trigger.
> The problem is that when the trigger is fired, it gives an error: "No
> transaction is active".
> I'm using SQL Express 2005 for both servers. The firewall is off in both
> machines, also, the MSDTS service is running.
> Could you tell me if I'm missing something?
> Thanks,
>
> " 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
> news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> >I am running SQL 2005 trigger and update the table in remote linked server.
> >This is working perfectly when the firewall is off. However when I turn on
> >the firewall, it won't run any more. I need the firewall on for the
> >security issue. How can I run this trigger with firewall on? Thanks.
> >
> >
>
>

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server
.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the securi
ty
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go t
o
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly
.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port throug
h
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.
Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John
|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John
|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John

Remote Connections with SQL Developers Edition

Hi,

I want to allow someone to update the data in a database being developed in SQL Developers Edition. The idea is while I'm developing the database the person who's database it is can do the data entry. The data monkey lives no where near me so I cannot keep keep updating a local copy there...

I've tried to set up and connect but the server keeps refusing the connection. So question is... can SQL Developers Edition allow remote connections... and if so, which I hope it can, whats the way to go about setting it up.

Far as I can tell I'm ok in regards to specifying the location of the server, the SQL user account is set up and has access to the database.... can anyone help.

Yes dev edition supports remote connections, you need to go into the
surface area config tool and enable them however|||

Thank you Euan, thank you very much.

Can I also ask... can it work over a secured connection using TCP/IP and what would be involved in setting that up?

|||Whats your definition of secure? SQL Server supports SSL over the wire
is one way of securing it.|||Thats what I wanted to hear. Need to try find out to set it up so I may post again. Thanks.|||Try the security forum, they will be able to help.|||good idea thanks