Archive data before deletion

Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris

Architecture question...can this be done?

All,
I have a question about setting up sql server in a particular
architecture. Below is a diagram of my situation:
[sql server] [XYZ server]
[clients A] -- [nic #1] [nic #1]
-- [clients B]
[nic #2] -- [nic #2]
The A clients can see the sql server fine. However, I want the B
clients to see the sql server also. The connection between the two
"#2" nic cards must be over a single tcp/ip port for security reasons.
Is there any way to do this? I am trying to avoid putting another
replicated sql server on XYZ server.
What about a proxy app on XYZ server? Couldn't I some how set up a
proxy to redirect a tcp/ip port traffic to the sql server? Is this
possible:
1. Set up the sql server to listen to both it's NIC's IP Addresses on
Port 1443.
2. Client B requests a connection from XYZ server's
NIC1_IP_ADDR,Port1443
3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
Am I on the right track? If anyone has any other suggestions in
solving this problem, I would really appreciate it.
Thankssorry my diagram was to big:
[sql server] [XYZ servr]
[clients A] --[nic #1] [nic #1]--[Clients B]
[nic #2]--[nic #2]|||Have you asked your network folks to use the route add command to configure
a route between clients B and sql server?
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:a59f0367-d8bc-4b9e-ae10-b5881750455d@.e25g2000prg.googlegroups.com...
> All,
> I have a question about setting up sql server in a particular
> architecture. Below is a diagram of my situation:
> [sql server] [XYZ server]
> [clients A] -- [nic #1] [nic #1]
> -- [clients B]
> [nic #2] -- [nic #2]
>
> The A clients can see the sql server fine. However, I want the B
> clients to see the sql server also. The connection between the two
> "#2" nic cards must be over a single tcp/ip port for security reasons.
> Is there any way to do this? I am trying to avoid putting another
> replicated sql server on XYZ server.
> What about a proxy app on XYZ server? Couldn't I some how set up a
> proxy to redirect a tcp/ip port traffic to the sql server? Is this
> possible:
> 1. Set up the sql server to listen to both it's NIC's IP Addresses on
> Port 1443.
> 2. Client B requests a connection from XYZ server's
> NIC1_IP_ADDR,Port1443
> 3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
> Am I on the right track? If anyone has any other suggestions in
> solving this problem, I would really appreciate it.
> Thanks
>|||On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you asked your network folks to use the route add command to configure
> a route between clients B and sql server?
>
No I have not. I looked at the route command and this is the command
for route add:
route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
destination^ ^mask ^gateway metric^ ^
Interface^
I am pretty sure the destination would be ip addr of nic#2 on the sql
server, but I am not sure about the other two parms (mask and
gateway). I assume that mask would be the subnet mask that could
identify the destination (nic#2 sqlserver). I am also assuming the
gateway would be nic#1 on XYZ Server, since it could reach the
mask... Am I on the right track?|||yes, you are correct. Your network admin should be able to help you here
though.
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:842426ce-f03c-4c73-b581-14370d77d2d7@.b40g2000prf.googlegroups.com...
> On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> Have you asked your network folks to use the route add command to
>> configure
>> a route between clients B and sql server?
> No I have not. I looked at the route command and this is the command
> for route add:
> route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
> destination^ ^mask ^gateway metric^ ^
> Interface^
> I am pretty sure the destination would be ip addr of nic#2 on the sql
> server, but I am not sure about the other two parms (mask and
> gateway). I assume that mask would be the subnet mask that could
> identify the destination (nic#2 sqlserver). I am also assuming the
> gateway would be nic#1 on XYZ Server, since it could reach the
> mask... Am I on the right track?

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe|||

I'm a NS newbie, creating my first NS app which indeed does notifications based on orders with line items (parent/child records).

However, I don't feel architecturally, pulling outside data (line items) is appropriate for either the content formatter or the delivery channel. IMHO, they should be responsible for (and only for) content formatting and delivery, respectively.

My intended solution is to flatten the order details much earlier in the process - in my custom event provider (which is a component responsible for data!). I need a custom event provider for other reasons (data coming from a web service), but this approach to handling parent/child records shouldn't require a cutom provider - I presume it'd work (even easier) with the SQL provider though I admit I haven't used that provider.

The trick is simple: I define the event class (and notification class) to contain the fields for the parent Order, and my order details (order line items) go into a single xml type column of this event class.

Simplified example snippet:

<EventClass>

<EventClassName>OrderData</EventClassName>

<Schema>

<Field>

<FieldName>CustomerName</FieldName>

<FieldType>nvarchar(128)</FieldType>

</Field>

<Field>

<FieldName>OrderDetails</FieldName>

<FieldType>xml</FieldType>

</Field>

Fill in OrderDetails with the aggregate line items and voila, a single "flat" event record with all the data needed for the notification. If all your source data is available in SQL, I imagine you could also do this flattening in the subscription action - pulling order line items out (as XML) based on the parent order ID and pushing them into the notifications view?

Note how this allows me to continue using the built-in XSLT formatter because the entire stream of event data is still just XML when it hits the XSLT. For line items that just an xsl for-each instruction loop to format the line items from the OrderDetails.

Note: to use this technique you need to turn off escaping in your ADF file so that XML flows through to the XSLT file during formatting:

<ContentFormatter>

<ClassName>XsltFormatter</ClassName>

<Arguments>

<Argument>

<Name>XsltBaseDirectoryPath</Name>

<Value>C:\SQL Notification Services\Orders\</Value>

</Argument>

<Argument>

<Name>XsltFileName</Name>

<Value>OrderSubmitted.xslt</Value>

</Argument>

<!-- we disable escaping so that our XML column (orderdetails) stays as

XML when being passed to the XSLT file for processing. The default is to

escape the embedded XML (e.g. "bar&gt;a bar&lt;/bar&gt;&lt" for "<bar>a bar</bar>" -->

<Argument>

<Name>DisableEscaping</Name>

<Value>true</Value>

</Argument>

</Arguments>

</ContentFormatter>

sql

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:
> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > Situation :
> > - via an application "xApp" a user has role "xRole" on a Db
> > - Via an application "yApp" the same user has an other "yRole" on the same
> Db
> > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > - The Application we can't change ( not owned by us )
> > Question :
> > Can we in any way assign a role ( change a role ) when the user connects ?
> This based on the application used. maybe via the connect string ?
> > any suggestion would be welcome.
> > Peter
> >
>
>|||I do not know, perhaps one of the others has more information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.
>
> "Wayne Snyder" wrote:
> > There is no trigger or event under which you can place code when a user
logs
> > in or changes databases, which is what you would need...
> >
> > Since roles are fixed, you'll have to grant both roles to the user... If
you
> > chould change the apps, you could use an application role, but since you
> > can't change the application it is not an option...
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Peter" <Peter@.discussions.microsoft.com> wrote in message
> > news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > > Situation :
> > > - via an application "xApp" a user has role "xRole" on a Db
> > > - Via an application "yApp" the same user has an other "yRole" on the
same
> > Db
> > > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > > - The Application we can't change ( not owned by us )
> > > Question :
> > > Can we in any way assign a role ( change a role ) when the user
connects ?
> > This based on the application used. maybe via the connect string ?
> > > any suggestion would be welcome.
> > > Peter
> > >
> >
> >
> >

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
Peter
There is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>
|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>
|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same D
b
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? T
his based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user lo
gs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If y
ou
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
>
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Application Roles and Bulk Insert

Is it possible to grant a user access to Bulk Insert via an Application Role
?
I have a situation where I dont want to grant users direct access to any
tables, but there is one table that they will need to do a bulk insert into.
I cant see anywhere that you can assign an approle as part of bulkadmin.
Any ideas of how to do this would be appreciated. Thanks.I don't believe you can use "Application roles" for this, but you can assign
"Bulk Insert Administrator" Server Role to any individual Server Login...
Under SQL Server Login Properties, Server Roles, one of the Server Roles is
Bulk insert Administrator.
"Jace" wrote:

> Is it possible to grant a user access to Bulk Insert via an Application Ro
le?
> I have a situation where I dont want to grant users direct access to any
> tables, but there is one table that they will need to do a bulk insert int
o.
> I cant see anywhere that you can assign an approle as part of bulkadmin.
> Any ideas of how to do this would be appreciated. Thanks.

Application not connecting to DB on restart of PC?

Hi,

i am stuck in a strange situation.
I

have successfully built my application setup through InstallSheild 12.

Which first installs SQL Express user define instance as a

pre-requisite and then install my application files. First time when i

run my application (without restarting the pc), it connects

successfully with user define instance.
But as i restart my PC and try to open my application, it is not connecting with database.
i am using the following command line to install SQL Express 2005 user define instance:

"/qn ADDLOCAL=SQL_Engine INSTANCENAME=MyInstance SECURITYMODE=SQL SAPWD="test" AUTOSTART=1"

if

i change Remote connection to "using both TCP/IP and named pipes"

through "SQL Server Surface Area Configuration" and then restart my pc

again. My application connects successfully with Database.

Please

guide me where i am getting wrong in building setup. do i have to add

something in my command line to not get this error

Please provide more information...

What error are you getting?

How are you connecting to SQL Express? (i.e. connection string)

What is different from the first time you connect at installation and the second time you connect after re-boot?

Are you using ODBC, OLEDB or Shared Memory to connect?

Mike

|||Hi,
thanks for the reply.
i am installing my installer on WinXP machine.
i am using the following connection string to connect with database.

Connection String:
--
Connection.ConnectionString = "packet size=4096;user id=sa;pwd=test; data source = (local)\\MyInstance;initial catalog = MyDB";

Error getting when connecting to SQL Mangmnt Studio:
-
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 -
Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

when first time i install , In "SQL Server Surface Area Configuration" it is checked on "Local Connection Only" and connects successfully.
When i restart my pc and open application it doesnt connect .I open "SQL Server Surface Area Configuration", still "Local Connection Only" is checked but when i check on "using both TCP/IP and named pipes".
and then start my applicaion it connects successfully.|||Hi,
i am using following namespace for connection object.
using System.Data.SqlClient;