Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year).
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group.
Best Regards,
James Jarupan
James,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison
|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon
|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>
|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon
|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>
sql

Archive Database

Hi
I have some questions about archive database in the real word case (now we h
ave only 4 gig of data and start to consider archieve database for the last
year).
1)Is it correct to archive just only transaction data? No need for some tabl
e such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is no
t in this group, please recommend me the right group.
Best Regards,
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>

Architecture question.

Hi
I have a Windows Form running in 40 clients around the city, those apps are
communicating with a central SQLDB hosted in a hosting company (each one is
sending data, waiting for data comparisons and receiving the data back). I
have been using a Web Service to make the link between my apps and the SQLDB
,
but now I want to connect my Windows Form apps directly to de SQLServer
(because I think the roundtrip is going to be faster, which is the most
important factor in my app)
My question to the architects and everyone:
Is this a good, secure, recommended, intelligent practice or this is a big
big mistake '
thks
kengood - Prototype it and see. Depends on the specific case usage and
implementation. You may find that the old style tightly coupled connection
is faster.
secure - Unencrypted xml is easier to intercept and read than unencrypted
native sql network calls. Using an encrypted protocol, it makes little
difference between the two.
recommended, intelligent practice or this is a big big mistake - Consider
the above and decide based on your requirement priorities.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:72C3EF9C-7DF4-4C85-A43B-8CE0C5E63388@.microsoft.com...
> Hi
> I have a Windows Form running in 40 clients around the city, those apps
are
> communicating with a central SQLDB hosted in a hosting company (each one
is
> sending data, waiting for data comparisons and receiving the data back). I
> have been using a Web Service to make the link between my apps and the
SQLDB,
> but now I want to connect my Windows Form apps directly to de SQLServer
> (because I think the roundtrip is going to be faster, which is the most
> important factor in my app)
> My question to the architects and everyone:
> Is this a good, secure, recommended, intelligent practice or this is a big
> big mistake '
>
> thks
> ken

Apriori algorithm [SQL Server 2005 and ASP.NET]

Hi

I am working on academic project using SQL Server 2005 & Visual studio 2005. Using Apriori algorithm to find the association between Patient City and likely diseases.

I have created PATIENT table with Patient_Id, Patient_name, Age, City attributes and Diseases table with Disease_Id, Disease_name. Connected these two tables, MANY - MANY [M:N]. Got a third relation with Patient_Id and Disease_Id attributes.

I am just inputting some dummy data into patient table and disease tables to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.

Here are my tables with attributes:

PATIENT ( Patient_Id, Patient_name, Age, City)

Diseases(Disease_Id, Disease_name)

[M:N] Got third below third relation bcz its Many to Many relationship

PATIENT_DISEASES(Patient_Id, Disease_Id)

I do think and believe that there is an efficient way of doing , instead of usin dummy data or using this relationships. I did check Microsoft Association algorithm and realised it is not Apriori algorithm.

Could you suggest the best or efficient way of doing this using SQL Server 2005?

Your help and insight into this matter is highly appreciated.

Regards

Sukumar Raju

The Association Rules is Apriori. What kind of rules are you looking for? Disease --> Disease, City --> Disease, Age -->Disease? All of the above?|||

Thank you.

Looking for association between City --> Disease.

I am just inputting some dummy data into patient table and disease table to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.

I think i have to write T-SQL statements to get the above algorithm work. Is there other way to achieve?

Is there best way other than simply writing T-SQL statements to implement Apriori algorithm work! Am i going in the right way?

Your suggestion and help is appreciated

regards

Sukumar raju

|||

You need to use SQL Server Analysis Services to use the Association Rules algorithm. You will launch BI Dev Studio and create a Analysis Services project containing a data source, data source view, and data mining structure - there are UI tools to do all of this - you don't need to write any TSQL.

I recommend checking out the resources at www.sqlserverdatamining.com - in particular the tutorial to learn how to use the DM functionality in SQL Server.

Thanks

-Jamie

|||

Thank you Mr.Jammie

I would do that. Could you give any resources that are useful to achieve my objective?

regards

Sukumar Raju

|||You can try the book I wrote with Zhaohui Tang - Data Mining with SQL Server 2005 - as a starting point. It describes how to create models of the type you need.|||

Thank you!

Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can

1) Create association model using BI in SQL Server 2005

2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.

hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Shouldbe getting in couple of days.

your suggestion and help is appreciated. Please advise.

regards

Sukumar Raju

|||

Sukumar Raju wrote:

Thank you! Jamie!

Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can

1) Create association model using BI in SQL Server 2005

2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.

hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Getting late...As my project submission date is just 5 days away.

your suggestion and help is appreciated. Please advise.

regards

Sukumar Raju

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
HernnDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
Hernn|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
atte,
Hernn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernn
|
|

Applying SP3

Hi

I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.

Could anybody tell me what and where is the impact when we install SP3..??

I would appreciate any kinda help.

Rgds

WilsonMake a backup of any production database (including master and msdb) before you apply sp3 (just to be paranoid). Apply the service pack, reboot NT, and go merrily on your way. No rebuilding or other "care and feeding" needed unless something goes dreadfully wrong during the application of the service pack.

-PatP|||just to be paranoid

I thought that was part of the job...

Oh, and someone once told me...in response to

"Do you think everyones out to get you? Are you Paranoid?"

"You're only Paranoid if you're wrong"

So what's the downside?

Application sending email

Hi:
I would like to know security implications on two different ways to enable
an application to email alerts.
1. Installing outlook client for application to call.
2. Using CDOSYS to point to SMTP server and send email using AD
username/password.
Thanks for any input.Option 1 -very bad choice. Outlook has too many security issues to safely in
stall on a server.
Option 2. klutzy but will/could work But waitk there are better choices.
Option 3. SQL 2000 download xp_smtpmail from [url]http://www.sqldev.net/xp/xpsmtp.htm[/
url]
Option 3. SQL 2005 -Use the included smtp mail server. Look up sp_send_dbmai
l in Books on Line.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message news:943EC1D1-1E2A-413A-8DF5-A5F9
19125DD9@.microsoft.com...
> Hi:
> I would like to know security implications on two different ways to enable
> an application to email alerts.
>
> 1. Installing outlook client for application to call.
>
> 2. Using CDOSYS to point to SMTP server and send email using AD
> username/password.
>
> Thanks for any input.|||Thanks for the information, but can you point me to some specifics about the
Outlook security issues. I am bringing up new SQL database with accounting
package. Package developers want to install Outlook on SQL server (has to b
e
2000 not compatiable with 2005). Need facts to support my posiition. If i
have no choice but to install outlook how can I secure?
If CDOSYS is klutzy is it more secure than outlook?
Thanks, Cindy
"Arnie Rowland" wrote:
[vbcol=seagreen]
> Option 1 -very bad choice. Outlook has too many security issues to safely
install on a server.
> Option 2. klutzy but will/could work But waitk there are better choices.
> Option 3. SQL 2000 download xp_smtpmail from http://www.sqldev.net/xp/xpsmtp.htm

> Option 3. SQL 2005 -Use the included smtp mail server. Look up sp_send_dbm
ail in Books on Line.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
>
> "Cindy" <Cindy@.discussions.microsoft.com> wrote in message news:943EC1D1-1
E2A-413A-8DF5-A5F919125DD9@.microsoft.com...|||Here is some sources of information about Outlook/MAPI issues.
http://support.microsoft.com/defaul...b;en-us;Q315886
Top Causes of SQL Server Downtime (Item #2)
http://www.sqlmag.com/Article/Artic...rver_40011.html
http://www.karaszi.com/SQLServer/info_no_mapi.asp
Google for "SQL Server" + MAPI + problems
Microsoft has 'dumped' MAPI for SQL Server 2005, instead using a built in
SMTP client.
--> check out xp_smtpmail. It's very easy to set up and very easy to use.
<--
The question to raise, "Is it better to use a mail client that has no
documented problems running or SQL Server, or a mail client that has a long
history of problems, including causing the SQL Server to fail."
What difference to the developers as long as there is a flexible way to send
messages.
And your last question, how can you secure Outlook... possibly never.
Recognize that as the number one mail client, it is the biggest target for
any hackers (new expliots are regularly found), and what do hackers want to
hack? -data servers.
If you can't tell, I have a 'little' bias on this issue... Outlook/MAPI on
SQL Server is a big AND unnecesssary mistake.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:464BAE44-2A86-4DDA-AF3C-9726B9AFAC03@.microsoft.com...[vbcol=seagreen]
> Thanks for the information, but can you point me to some specifics about
> the
> Outlook security issues. I am bringing up new SQL database with
> accounting
> package. Package developers want to install Outlook on SQL server (has to
> be
> 2000 not compatiable with 2005). Need facts to support my posiition. If
> i
> have no choice but to install outlook how can I secure?
> If CDOSYS is klutzy is it more secure than outlook?
> Thanks, Cindy
> "Arnie Rowland" wrote:
>|||Thanks for the info.
Cindy
"Arnie Rowland" wrote:

> Here is some sources of information about Outlook/MAPI issues.
> http://support.microsoft.com/defaul...b;en-us;Q315886
> Top Causes of SQL Server Downtime (Item #2)
> http://www.sqlmag.com/Article/Artic...rver_40011.html
> http://www.karaszi.com/SQLServer/info_no_mapi.asp
> Google for "SQL Server" + MAPI + problems
> Microsoft has 'dumped' MAPI for SQL Server 2005, instead using a built in
> SMTP client.
> --> check out xp_smtpmail. It's very easy to set up and very easy to use.
> <--
> The question to raise, "Is it better to use a mail client that has no
> documented problems running or SQL Server, or a mail client that has a lon
g
> history of problems, including causing the SQL Server to fail."
> What difference to the developers as long as there is a flexible way to se
nd
> messages.
> And your last question, how can you secure Outlook... possibly never.
> Recognize that as the number one mail client, it is the biggest target for
> any hackers (new expliots are regularly found), and what do hackers want t
o
> hack? -data servers.
> If you can't tell, I have a 'little' bias on this issue... Outlook/MAPI on
> SQL Server is a big AND unnecesssary mistake.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Cindy" <Cindy@.discussions.microsoft.com> wrote in message
> news:464BAE44-2A86-4DDA-AF3C-9726B9AFAC03@.microsoft.com...
>
>|||Understanding that Outlook has security issues, I personally have used SQL
Mail in the past with the Outlook client and had great success. Although it
may be one of the top reasons for 'downtime' (if you call a reboot
downtime), how much downtime is that? If you have 99.9% uptime and SQL Mail
is responsible for .1% downtime, is that acceptible? It was for me.
If you stay on top of your Outlook updates, restrict access to the xp_'s
(xp_sendmail, for example), and do your homework on SQL Mail, there is some
reward to offset the risk. There are factors that may sway you one way or
another depending on your situation.
My server sent an average of around 100 emails per minute - with SQL Mail
and the Outlook client.
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:943EC1D1-1E2A-413A-8DF5-A5F919125DD9@.microsoft.com...
> Hi:
> I would like to know security implications on two different ways to enable
> an application to email alerts.
> 1. Installing outlook client for application to call.
> 2. Using CDOSYS to point to SMTP server and send email using AD
> username/password.
> Thanks for any input.

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denie
d
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/defaul...;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:

>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission deni
ed
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 an
d
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password}
,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=’False " (despite I think this is only for .Net and I'm tried wit
h
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:[vbcol=seagreen]
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/defaul...;en-us;Q229564.
>--Mary
>
>[quoted text clipped - 68 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1

Application role help

Hi
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
Manoj
See sp_helprotect in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj

Append Query Problems

Hi

I am doing an A level database project. I need to build a system to deal with ordering and selling products.

I am trying to make an append query that will append all the products bought in my "invoices" table to my "archive" table. I want invoices that are three months old to be transferred the the "archive" table. I have been trying to put a criteria in the query for the Date field but I haven't managed to find an expression that works.

I'm sorry if this is too simple and you guys think im just being stupid.
I figured it was worth asking on here!! It's really bugging me.Show us what you got so far. Also ... look up dateadd and datediff in BOL.|||Sorry, what is BOL?
"Date"<=#01/01/2006#

Is that what you mean by show? I don't know how to show the whole query :-s|||Can you write a SELECT statement to retrieve the rows that you want to archive?

-PatP|||Thanks but I don't know what a select statement is! Is it the same as a select query? there's something about that in one of my books.|||Is this project in Access? You may need to ask over in the Access forum...|||Yes it is. I realised after I posted here that there is an access forum too. I have posted in there now thanks!