Best practices for remote users

Hi,
What is the best practice to allow remote users that are not part of our
domain to connect to a specific database on our server? Do we use Windows
Authentication by adding them to the domain or do we use sql authentication.
These users are in a different company and they will be logged on into their
active directory.
I would love to use windows authentication by adding them into our domain
but when setting up ODBC in the control panel, there is no option to send the
user credentials. ODBC seems to want to use the credentials of the currently
logged on user.
These users will be using Microsoft Access to run reports.
Thanks.
Your problem is the users are not logging on to your domain when they start
their machines. I doubt you can get Access to set up a connection using a
separate Windows Account - your best bet is to go with a SQL login - or you
could investigate using a VPN connection to see if that works...
"STech" <STech@.discussions.microsoft.com> wrote in message
news:57BAC952-CCBD-475D-AAD9-F81D8A696D2B@.microsoft.com...
> Hi,
> What is the best practice to allow remote users that are not part of our
> domain to connect to a specific database on our server? Do we use Windows
> Authentication by adding them to the domain or do we use sql
authentication.
> These users are in a different company and they will be logged on into
their
> active directory.
> I would love to use windows authentication by adding them into our domain
> but when setting up ODBC in the control panel, there is no option to send
the
> user credentials. ODBC seems to want to use the credentials of the
currently
> logged on user.
> These users will be using Microsoft Access to run reports.
> Thanks.
|||Hi STech,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Best design for a service that will monitor db

We have an existing database that is constantly receiving updated
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>

best approach for "trigger on commit"?

(I've searched the forum but haven't found anything that seems to address my specific question)

We have multiple tables that get updated as part of a transaction.

After all the data is added succesfully there is processing that needs

to be done, so we'd like to use a trigger to kick it off automatically. The schema can be thought of as including a "master report" table that gets updated once whenever a new report is submitted and multiple related tables each corresponding to different report sections. The PK-FK relations imply that the master report will be updated prior to the subsidiary sections, but the exact order of updates to the subtables is not defined.

I thought of attaching a trigger to the master report table but I can't find an approach or mechanism that would allow us to defer the triggered code until after all the tables have been updated.

It almost seems like I want a transaction trigger. Something that would be executed before or after a COMMIT TRAN. But the

post-processing code does not have to be deferred until after the

transaction is committed, it just mustn't be started until all the

tables participating in the transaction have been updated, otherwise

all the data needed won't be present.

There are several work-arounds that come to mind, but none are as

elegant as having a TRIGGER ON COMMIT capability. I'd be interested in

how other people have solved this problem.

Here's some of the work-arounds we came up with

1. Identify the last table to be updated, and place a trigger on that. Ugly for obvious reasons and very prone to breaking.

2. Create an "update history" table with a trigger on it. After the

actual transaction commits, a row should be added to this table by the

db user. Less ugly, but still not pretty.

3. Variation on #2 -- after an update transaction, have the database user call a stored procedure. Just as ugly as #2

4. Create a view that is used to write through to the underlaying tables, and have the trigger on the view. Unfortunately the large amount of data (approx 20 tables participate in one update transaction) makes this very unattractive. (Imagine an INSERT statement with 300 values!?)

Thanks in advance for suggestions and help

TerryPesonally, I would recommend any solution that does not use Triggers. Triggers are evil. They are a maintenance nightmare. After you finish your "transaction", you can call a stored proc that takes care of the rest of the updates which is a more controlled way of doing than doing it in a trigger. Do a simple google search on issues with triggers and you will be convinced enough to not use them.|||

We, I cannot agree with your feelings on triggers (Cursors are evil, not triggers. Triggers are just a bit difficult :) I do agree that this is not the place for a big trigger (considering how SQL Server triggers work.)

An alternative that I might suggest if this can be done asynchronously is that you have a job that queries your tables to see when the transaction is finished (either you check for the existance of data in all tables, or you write some sort of control row (possibly using triggers here) when you do each insert into the other tables. Then the process can check to see when all of the data for a "transaction" is completed and do your processing.

|||I can second Louis, triggers always had and will have a bad reputation because of the wrong implementation of users. Sure, in some cases they behave slow, they act syncronously (which can real nightmare if not considered) and are fired per statement not per row, so a bit implementation afford is needed here, but triggers can help your through the day for enforcing business rules which can′t be accomplished through any middle tier (if the database is directly access by several applications). So I would say that they are as evli as you force them to be :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||After the database is developed it may have data added by an unknown number of subcontractors. So it is important that the "api" be as simple as possible and that the database be as intelligent as possible. Building business logic into the database in the form of SP and triggers has clear advantages for us over an approach that depends on the goodwill of users to follow our suggested/required calling structure.

In other words, having a document that says "after inserting all of a report call SP after_insert_processing()" opens up the door for someone to forget to call the SP. Having a mechanism in place that detects the operation and automatically calls the SP is much more attractive to us.

Hmmm.....|||BTW thanks for all of the suggestions. We may go with a job that checks the time of the last update and determines from that if the post-insert processing should be done.|||

You can do something like below:

1. Create a tracking table

2. Insert a row from the tracking table for each operation on the other table(s) (Need not be at insert level. You can determine the granularity)

3. Have the insert trigger on the tracking table determine the matching conditions to fire the SP. This can be done by checking for count of specific number of operations per report for example. The post_insert_processing SP can be fired using an on-demand SQLAgent job so that it is asynchronous.

You could do away with the tracking table approach and simply do the check in every SP that inserts into the other table(s) also. Either way you can centralize the logic to check in a SP (by using simple queries against the required table(s)).

|||

My first question would be..."How do you currently ensure all information is updated correctly?"
My second question would be..."Is this a manual process that you want to include in your automation?"

It sounds as if there is some form of linear progression with a definitive end but you want to avoid this assumption for fear of change.

So...to satify your anxieties, create an independant action (the approach is insignificant).

Forget about the clever trickery or sly workaround, code a 101 solution to a 101 problem.

Adamus

Benefits of upgrading SQL 7 to 2K

Could someone please let me know the specific 'benefits' of upgrading a SQL 7 system to a 2K? For instance if any of you have done this, did you notice a significant change within your system(s)?
Thanks!!!Well, as an Enterprise solution, 7.0 cannot be reliably clustered. Performance may improve on certain queries, but don't be surprised to see a degradation, though it's pretty rare. The most benefits are for applications that are either being newly developed or the ones that can be modified without infringing your support agreement with your vendor. The reason is added functionality. But if your system needs only backups and occasional reindexing, - don't fix what ain't broke :)|||There are lots of benefits to upgrading and many articles available on the subject...

Here are a few links that will give you some background:

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28000409

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/whatsnew/wn_whatnew_7im0.asp?frame=true

CPN|||Originally posted by NotAvg1
Could someone please let me know the specific 'benefits' of upgrading a SQL 7 system to a 2K? For instance if any of you have done this, did you notice a significant change within your system(s)?

Thanks!!!

There are a few keywords, functions, etc. that are in 2K that aren't in 7. We have one vendors application that uses some of them, but they are good enough to have an MSDE version that we can use.

I would say that unless you are worried about clustering, or a specific application requires it, I don't think there is enough justification.

Besides I bet a 2K3 or 2K4 version is probably in alpha testing somewhere. That way you could just jump versions.

being more specific

Hi all,

i want to write a trigger which returns a row to the application whenever a row is inserted into a table.

E.g.

When row R1 is inserted into a table T1.

the trigger should return the R1 to the application.

Wanna be coding monkey...

hi chaman,

its a bad practive for a trigger to be recordset returning.

trigger are best used for sending the result somewhere in the database

or for task such as maintaining the database consistency after an insert.

what are you going to do with it. can you specify what's your scenario so we can provide more help

if this is really what you need. you should write a stored procedure instead

regards,

joey

|||

Chaman:

I agree completely with Joey's position -- returning result sets from a trigger is in general a very bad idea. Also, there are other things that you need to consider should you continue with the result-set-from-a-trigger approach. It seems to me from your post that you have not considered impact whenever a single insert statement inserts multiple records into the table.

Are you motivated to return the row by any chance because you are using an identity column?

Dave

|||

Yes.. I also accept both Joeydj & Mugambo's comment..

If you want to read all about SQL Server Trigger visit this MSDN magazine Data Point article http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx#S4

http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/

if you don;t have time to read these article read the summary of these article on

http://weblogs.asp.net/akinney/archive/2003/11/14/37509.aspx

But really we don't know the purpose of Chaman's need...I will give the solution but before doing this read all these article and choose your decesion.( i am not ready to say its not possible at all in sql server )

on your trigger use the following statement

select * From Inserted (note: this statement only work on Trigger)

|||

Hi all,

thanks for your help,

well as i am new in this SQL let me be clear on my requirements..

1. we have a table T1, when a new row R is added in T1,

i have a service who triggers an action (say beep) depending on the entities in R,

i want a trigger who will send the data in R of T1 to that service.

Now, question 1 - is it possible to write sucha trigger?

2. if yes, how? how can a table return a value to a service.

Regards,

chaman.

|||How is this service listening for the prompts?|||

i think you need to create a service that listens or monitor the event in the db.

any way if your into service orinted design

it is not definetely the trigger that you will have to use.

you might as well consider "sql server notification services" and/or the service broker

beginner in SQL Server 2000

Hi,
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
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
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Before Insert Trigger cancels insert

Is it possible for me to write a before insert trigger which will prevent insert if the data doesn't match specific criteria? What would the syntax look like?SQL Server triggers are always AFTER triggers, but you can rollback the insert.

If some condition causes you to want to fail the insert, you can just rollback the transaction

From Books Online:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
 WHERE name = 'employee_insupd' AND type = 'TR')
 DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @.min_lvl tinyint,
 @.max_lvl tinyint,
 @.emp_lvl tinyint,
 @.job_id smallint
SELECT @.min_lvl = min_lvl,
 @.max_lvl = max_lvl,
 @.emp_lvl = i.job_lvl,
 @.job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
 JOIN jobs j ON j.job_id = i.job_id
IF (@.job_id = 1) and (@.emp_lvl <> 10)
BEGIN
 RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
 ROLLBACK TRANSACTION
END
ELSE
IF NOT (@.emp_lvl BETWEEN @.min_lvl AND @.max_lvl)
BEGIN
 RAISERROR ('The level for job_id:%d should be between %d and %d.',
 16, 1, @.job_id, @.min_lvl, @.max_lvl)
 ROLLBACK TRANSACTION
END

|||I just want to add that SQL Server 2000 introduced "INSTEAD OF" triggers. These triggers fire BEFORE the action. Well, more correctly they fire instead of the updating action.

Terri|||True. The name has put me off, and so I have not really ever used them, but in effect they can be used exactly like a before trigger. Cool.

From a design standpoint I would have preferred a true BEFORE UPDATE (INSERT/DELETE) trigger to save me some work in the trigger if I want the transaction to go through, but this is certainly close.

Thanks again.|||does SQL Server not have a "Create or Replace" clause? i notice in the sample code that there is logic to do just that.

if i write an INSTEAD OF trigger, how do i insert into the same table without causing recursion? or is SQL Server smart enough to avoid that? i haven't seen any examples in the help files for what i want to do.