Access Denied error in SQL Server 2000 db

Hi!
I have stored procedure that executes BULK INSERT command inside.
Recently I migrated my sql 7 database to sql 2000 SP2.
Now when running this stored procedure from both application and Query
Analyzer I'm getting this message:
Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
not be opened. Operating system error code 5(Access is denied.).
I found article on Microsoft web site that this is a known bug and it
only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
Why am I getting this error in SQL Server 2000?
Should I apply the latest SP to SQL 2000 to fix this security issue?
Thank you,
YelenaI checked all permissions.
SQL Server service is running under domain account and this user has
access to the share where the file is located.
Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
as sa.
I can't figure out why I'm getting Access Denied error.
Please help.
enaglozman@.yahoo.com (Lena) wrote in message news:<fc1e8071.0403160709.2dfad366@.posting.goo
gle.com>...
> Hi!
> I have stored procedure that executes BULK INSERT command inside.
> Recently I migrated my sql 7 database to sql 2000 SP2.
> Now when running this stored procedure from both application and Query
> Analyzer I'm getting this message:
> Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
> because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
> not be opened. Operating system error code 5(Access is denied.).
> I found article on Microsoft web site that this is a known bug and it
> only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
> Why am I getting this error in SQL Server 2000?
> Should I apply the latest SP to SQL 2000 to fix this security issue?
> Thank you,
> Yelena|||Does it fail if you are logged on as the account running MSSQLServer
service? Also, that account will need read permissions to the share/file
that you reference:
'\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close'
Steve
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:fc1e8071.0403230855.57f26b9a@.posting.google.com...
> I checked all permissions.
> SQL Server service is running under domain account and this user has
> access to the share where the file is located.
> Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
> as sa.
> I can't figure out why I'm getting Access Denied error.
> Please help.
> enaglozman@.yahoo.com (Lena) wrote in message
news:<fc1e8071.0403160709.2dfad366@.posting.google.com>...sql

Access Denied txt file on other server

Hi and Help!!
I'm using Bulk Insert to read a text file that is on a different server. I
get an access denied message.
- If I change the name to a non-existant file,
I get an error that it can't find that file, so it can see it.
- I can successfully read a copy that I stored on the SQL server server.
I pretty much have whatever rights I need, personally, but I'm guessing I
need to set something up elsewhere.
Any suggestions?
Thanks,
ArtTry checking the account that the server is running as, for example if
LocalSystem is being used, it may not have the permissions to access the
file. I always use a service account and grant permissions to a specific
folder on the server to pass to.
"Art" wrote:

> Hi and Help!!
> I'm using Bulk Insert to read a text file that is on a different server.
I
> get an access denied message.
> - If I change the name to a non-existant file,
> I get an error that it can't find that file, so it can see it.
> - I can successfully read a copy that I stored on the SQL server server.
> I pretty much have whatever rights I need, personally, but I'm guessing I
> need to set something up elsewhere.
> Any suggestions?
> Thanks,
> Art|||Josh,
Thanks for the suggestion -- I won't be able to try it until Tuesday.
Unfortunatly I still don't know much about how to use SQL Server. I've
learned enough SQL and enough about Stored Procedures, but I don't actually
know too much about the system itself. I guess that's something that I had
better change.
Any suggestions on a good book? I'm currently using 2000, but in a few
months will upgrade to 2005.
Thanks again,
Art
"Josh Crosby" wrote:
> Try checking the account that the server is running as, for example if
> LocalSystem is being used, it may not have the permissions to access the
> file. I always use a service account and grant permissions to a specific
> folder on the server to pass to.
> "Art" wrote:
>|||sure, great book on SQL 2005 written by Michael Otey i'm writing a review
for, here's the info.
SQL Server 2005: Developer's Guide
ISBN: 0-07-226099-8
--
Josh Crosby
"Art" wrote:
> Josh,
> Thanks for the suggestion -- I won't be able to try it until Tuesday.
> Unfortunatly I still don't know much about how to use SQL Server. I've
> learned enough SQL and enough about Stored Procedures, but I don't actuall
y
> know too much about the system itself. I guess that's something that I ha
d
> better change.
> Any suggestions on a good book? I'm currently using 2000, but in a few
> months will upgrade to 2005.
> Thanks again,
> Art
> "Josh Crosby" wrote:
>

Access 2000, SQL Server 2000, Bulk Insert

I am pulling information from a DB2 table through a pass-through query
in Access 2000. I am going to create a comma delimited file that I want
to use Bulk Insert to put into SQL Server 2000.
I have the query to pull the data, all is well. How do I program in VBA
(Acess 2000) to do a bulk insert into a SQL Server table. I have 100000
records that I do this to every day. Bulk Insert seems to be the fastest
way. I will bulk insert the comma delimited file.
I am on a PC, not the SQL Server for the bulk insert. I cannot access
the mainframe DB2 tables from the SQL Server. My PC is the middle man
right now. How do I effectively do the bulk insert.
Does anybody have any experience in doing this, if so, can you post some
code to assist.
Thanks, Lyners
--
Posted via http://dbforums.comOne more thing, I have the code written so that it pulls the data and
loads the records 1 by 1. It takes to long and I keep blowing up the
transaction file. That is why I want to do the bulk insert. 1 record for
the transaction file. (AT least that is what I have read).
Posted via http://dbforums.com

Access .adp :How to INSERT all but KEY violations

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?For example:

INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

(where key_col is the primary key).

--
David Portas
SQL Server MVP
--

abt Stored Procedure ...

hello guys ..

today i came with a Common question...

i am developing a website in that, I am using Stored procedure for all queries ( select, insert, update, delete )

is it good to run all queries in stored procedure ... or we should run only the SELECT queries by SP ... i need ur valuable suggestion ...

cheers ...i am waiting for some experienced person's answer ... is anybody here ??|||

Quote:

Originally Posted by rameshcse2005

i am waiting for some experienced person's answer ... is anybody here ??

hi,
You can use any sql statement inside your stored procedure...

According to me if you use stored procedures in your applications it will be good, if you need any database change just manipulate your sp, no need of rebuild or recompile your application.. all you need is organizing your application and stored procedures. better the design you have better the result you get|||You can use all the DML statments inside a procedure.|||Thank you very much for ur valuable suggestion ... :-)

and i have another doubt ?

how to pass multiple value in same parameter in SQL stored procedure ??

for example i have to write stored procedure for this query

DELETE FROM tableName where ID in (id1,id2,id3) ?|||You need to pass a refcursor to the procedure to dynamically pass any number of parameters for the IN operator..|||Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)|||

Quote:

Originally Posted by rameshcse2005

Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)

hi
you can do this as follows

send the id as a string seperated by ,(comma)

in your stored procedure use the following query

DELETE FROM tableName where ID in
( select id from tableName
where (','+isnull(@.parameter,convert(varchar(10),Id) )+',') like
('%,'+convert(varchar(20),Id)+ ',%')
)

In the above query if you pass null value to the @.Parameter then all the records will be deleted, if you send the id values seperated by coma then specific records only will be deleted

about trigger ..

what's the problem?
create trigger T_credito
after insert on movimento
declare
periodo number;
begin
select durata into periodo from movimento
where codcliente=:new.codcliente and codfilm=:new.codfilm and codnegozio=new.codnegozio;
if periodo = 0
then
update CLIENTE
set credito=credito - 2
where user_id= :new.codcliente;
else
update CLIENTE
set credito=credito - (periodo *3)
where user_id= :new.codcliente;
end;
thank you ElisaHi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)|||Originally posted by LKBrwn_DBA
Hi,

This is the problem:

where codcliente=:new.codcliente and codfilm=:new.codfilm
and codnegozio=new.codnegozio;

In an after insert trigger you no longer have access to the :new and :old variables.

;)

I understand but what I can need? It's very important|||You will need to create one package and THREE triggers, something like this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
PV_Codcliente Codcliente_Typ;
PV_Codfilm Codfilm_Typ;
PV_Codnegozio Codnegozio_Typ;
Cnt_Cte Pls_Integer;
End;
/
Create Trigger Tb_Credito
Before Insert On Movimento
Begin
Credito_Pkg.Cnt_Cte := 0;
Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;
End;
/
Create Trigger Tar_Credito
After Insert On Movimento For Each Row
Begin
Credito_Pkg.Cnt_Cte := Credito_Pkg.Cnt_Cte + 1;
Credito_Pkg.PV_Codcliente(Credito_Pkg.Cnt_Cte) := :New.Codcliente;
Credito_Pkg.PV_Codfilm(Credito_Pkg.Cnt_Cte) := :New.Codfilm;
Credito_Pkg.PV_Codnegozio(Credito_Pkg.Cnt_Cte) := :New.Codnegozio;
End;
/
Create Trigger Ta_Credito
After Insert On Movimento
Declare
Periodo Number;
P Pls_Integer;
Begin
For P In 1..Credito_Pkg.Cnt_Cte Loop
Select Durata
Into Periodo
From Movimento
Where Codcliente = Credito_Pkg.PV_Codcliente(P)
And Codfilm = Credito_Pkg.PV_Codfilm(P)
And Codnegozio = Credito_Pkg.PV_Codnegozio(P);
If Periodo = 0
Then
Update Cliente
Set Credito = Credito - 2
Where User_Id=Credito_Pkg.PV_Codcliente(P);
Else
Update Cliente
Set Credito = Credito - (Periodo *3)
Where User_Id=Credito_Pkg.PV_Codcliente(P);
End If;
End Loop;
End;
/

:cool:

NOTE: Is not optimized...|||Originally posted by LKBrwn_DBA
You will need to create one package and THREE triggers, something like this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
PV_Codcliente Codcliente_Typ;
PV_Codfilm Codfilm_Typ;
PV_Codnegozio Codnegozio_Typ;
Cnt_Cte Pls_Integer;
End;
/
Create Trigger Tb_Credito
Before Insert On Movimento
Begin
Credito_Pkg.Cnt_Cte := 0;
Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;
End;
/
Create Trigger Tar_Credito
After Insert On Movimento For Each Row
Begin
Credito_Pkg.Cnt_Cte := Credito_Pkg.Cnt_Cte + 1;
Credito_Pkg.PV_Codcliente(Credito_Pkg.Cnt_Cte) := :New.Codcliente;
Credito_Pkg.PV_Codfilm(Credito_Pkg.Cnt_Cte) := :New.Codfilm;
Credito_Pkg.PV_Codnegozio(Credito_Pkg.Cnt_Cte) := :New.Codnegozio;
End;
/
Create Trigger Ta_Credito
After Insert On Movimento
Declare
Periodo Number;
P Pls_Integer;
Begin
For P In 1..Credito_Pkg.Cnt_Cte Loop
Select Durata
Into Periodo
From Movimento
Where Codcliente = Credito_Pkg.PV_Codcliente(P)
And Codfilm = Credito_Pkg.PV_Codfilm(P)
And Codnegozio = Credito_Pkg.PV_Codnegozio(P);
If Periodo = 0
Then
Update Cliente
Set Credito = Credito - 2
Where User_Id=Credito_Pkg.PV_Codcliente(P);
Else
Update Cliente
Set Credito = Credito - (Periodo *3)
Where User_Id=Credito_Pkg.PV_Codcliente(P);
End If;
End Loop;
End;
/

:cool:

NOTE: Is not optimized...

This line of code isn't exactly because I have an error and I don't know what I can do.
The error is:
Error: ORA-06531: Reference to uninitialized collection
ORA-06512: at "GL15.TB_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TB_CREDITO'

Thank you for your help. Elisa|||True,

Change these lines:

Credito_Pkg.PV_Codcliente.Delete;
Credito_Pkg.PV_Codfilm.Delete;
Credito_Pkg.PV_Codnegozio.Delete;

To this:

Credito_Pkg.PV_Codcliente := Credito_Pkg.Codcliente_Typ();
Credito_Pkg.PV_Codfilm := Credito_Pkg.Codfilm_Typ();
Credito_Pkg.PV_Codnegozio := Credito_Pkg.Codnegozio_Typ();

;)|||I know I'm boring you but with the new line there is a new error:

Error: ORA-06533: Subscript beyond count
ORA-06512: at "GL15.TAR_CREDITO", line 3
ORA-04088: error during execution of trigger 'GL15.TAR_CREDITO'

and I don't understand what's the problem.
If you can help me I'm very happy.
Thank you,
Elisa|||Since the trigger does update operations you will be able to make the trigger a before insert. If the insert fails by any chance there is no problem since the effect of the trigger is also reversed.|||I don't understand .. I'm afraid .. can you explain another time.
Thank you|||Elisa,

Pls change this:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type;
Type Codnegozio_Typ Is Table Of Movimento.Codnegozio%Type;
...etc...

To This:

Create Package Credito_Pkg
As
Type Codcliente_Typ Is Table Of Movimento.Codcliente%Type Index By Binary_Integer;
Type Codfilm_Typ Is Table Of Movimento.Codfilm%Type Index By Binary_Integer;
Type Codnegozio_Typ Is Table Of
Movimento.Codnegozio%Type Index By Binary_Integer;
...etc...

:D|||Thank you very much, Elisa

about Transaction's problem

There is a table named users in my database.
It has two colume:
userName varchar(20)
age int
now I execute the follow transaction:
begin tran
insert users (userName,age) values ('mark',25)
insert users (userName,age) values ('jack',2577777777777767777)
commit tran
obviously,the second setence has an overflow error,
the question is:has the first record been inserted into the users table?
why?"κÊöÀÚ" <weisl@.netstars.com.cn> wrote in message
news:%23T$6pCrPGHA.140@.TK2MSFTNGP12.phx.gbl...
> There is a table named users in my database.
> It has two colume:
> userName varchar(20)
> age int
> now I execute the follow transaction:
> begin tran
> insert users (userName,age) values ('mark',25)
> insert users (userName,age) values ('jack',2577777777777767777)
> commit tran
> obviously,the second setence has an overflow error,
> the question is:has the first record been inserted into the users table?
> why?
>
Because you told it to. You need error checking (or try/catch in SQL 2005)
to prevent execution from moving from the line that generates the error to
the line that commits the transaction.
David

about system variable

i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>

about subsuery of tsql (or anything like oracle's sequence in sql server )

exec('insert
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

about SqlTransaction

updatestring = "Update [Flow] set received=1 where flowid=@.flowid"
insertstring = "Insert into [Flow] (barcode, copyid, floworder, recvdate, recvuser, recvdepartment) "
insertstring += "values (@.barcode, @.copyid, @.floworder, @.recvdate, @.recvuser, @.recvdepartment)"

is it can't use sqltransaction in the same table ?

when i not apply the sqltransaction, then the about two queries can work success, but when i apply the sqltransaction, then have an error in following.

error :
Object reference not set to an instance of an object

Source Error:

Line 278: message.Text = "Recebido o documento " & barcodetx.Text & "!!"
Line 279: Catch ex As Exception
<red>Line 280: objtransaction.Rollback()</red>
Line 281: message.Text = "Não se conseguiu registar!!"
Line 282: FinallyHi

You haven't posted any of the real code where stuff is happening.
You are probably best of wrapping the whole thing inside a stored proc and calling that.

about processXMLdata sample,how to insert a return in end of each row data of the result text fi

I am newbie, having a job to handle some XML files into sql server 2005. In the procXMLdata sample,,pls tell me how to insert a return in end of each row data of the result text file?

I am not familiar with this sample, but my guess is that you need to use Ragged Right Flat File format and choose {CR}{LF} as the row delimiter.

Give us more of the problem's context, if this is not what you are looking for.

Thanks.

|||

hi,

Thanks for your reply.

The processXMLdata sample was put in the sample directory of ms sql server,which just converted a xml file into a txt file ,using two xml task. The first was to preform XPATH method,output a variable, and the second xml task was to do XSLT method ,input the variable above, output a txt.

I know XML little , i guess the problem may be on propery of the second XML task.

About my way to insert rows.

I have heard that the most time consuming task into a DB is the when we use
the Insert Statatement,
Is that true?
Ok I have an application that sends from 10 to 500 rows to the DB, those
rows are compared again a fixed value and then they are inserted to the DB i
f
the comparison is ok.
I would like to do all that inside the DB (a SP) but I don’t know how, so
I
created a WebService to do that, my point is that I’m not inserting row by
row instead I hold the rows in a Dataset (memory) and later I make one big
insert to the DB.
My question is should I leave that code like this or should go to the DB in
order to insert row by row to gain some performance?
Can you think a better way to do that, because that is the heart of my app
and I want to minimize the time it takes.
Thnks.
Kenny M.Hi Kenny
Please let me know what kind of comparision is that u are doing before
inserting rows into your database.
Just try avoiding bringing data to the front-end if its only a validation
part.
You can write a query as:
INSERT INTO <TABLE>
SELECT <Columns>
FROM <another table>
<WHERE Condition>
alternatively you can also write INSTEAD OF Triggers, if you want to check
the records before they are inserted
hope this will solve the problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Kenny M." wrote:

> I have heard that the most time consuming task into a DB is the when we us
e
> the Insert Statatement,
> Is that true?
> Ok I have an application that sends from 10 to 500 rows to the DB, those
> rows are compared again a fixed value and then they are inserted to the DB
if
> the comparison is ok.
> I would like to do all that inside the DB (a SP) but I don’t know how, s
o I
> created a WebService to do that, my point is that I’m not inserting row
by
> row instead I hold the rows in a Dataset (memory) and later I make one big
> insert to the DB.
> My question is should I leave that code like this or should go to the DB i
n
> order to insert row by row to gain some performance?
> Can you think a better way to do that, because that is the heart of my app
> and I want to minimize the time it takes.
> Thnks.
> --
> Kenny M.|||Hi, Well
In order to understand it better, the table Category is like this
CategoryID Number SetAmount
1 0 1000
1 ... ...
1 999 2000 For each Category there
are 1000 numbers
2 0 200
2 ... ...
2 999 3000
.. ... ...
n 0 - 999 ...
e.g
I send 100 rows to the method . The rows are like this
ID,DATETIME, NUMBER,AMOUNT,CATEGORYID
For each row I have to go to the categorytable look for the CATEGORYID then
look for the NUMBER and ask if the AMOUNT is > SetAmount. If so I have to
Insert the Whole Record into another table.
At this time I'm doing that process inside a WService.
I'm not inserting row by row, instead I'm saving the valid row in a DataSet
to later Insert them to a Table.(Using Fill method)
Any sug.
thks.
Kenny M.
"Chandra" wrote:
> Hi Kenny
> Please let me know what kind of comparision is that u are doing before
> inserting rows into your database.
> Just try avoiding bringing data to the front-end if its only a validation
> part.
>
> You can write a query as:
> INSERT INTO <TABLE>
> SELECT <Columns>
> FROM <another table>
> <WHERE Condition>
> alternatively you can also write INSTEAD OF Triggers, if you want to check
> the records before they are inserted
> hope this will solve the problem
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Kenny M." wrote:
>

About my way to insert rows.

I have heard that the most time consuming task into a DB is the when we use
the Insert Statatement,
Is that true?
Ok I have an application that sends from 10 to 500 rows to the DB, those
rows are compared again a fixed value and then they are inserted to the DB i
f
the comparison is ok.
I would like to do all that inside the DB (a SP) but I don’t know how, so
I
created a WebService to do that, my point is that I’m not inserting row by
row instead I hold the rows in a Dataset (memory) and later I make one big
insert to the DB.
My question is should I leave that code like this or should go to the DB in
order to insert row by row to gain some performance?
Can you think a better way to do that, because that is the heart of my app
and I want to minimize the time it takes.
Thnks.
Kenny M.Hi Kenny,
Maybe this will give you a few solutions.
http://www.sommarskog.se/arrays-in-sql.html
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:26D58C74-C301-466F-9B58-AC3C2A84AAFA@.microsoft.com...
>I have heard that the most time consuming task into a DB is the when we use
> the Insert Statatement,
> Is that true?
> Ok I have an application that sends from 10 to 500 rows to the DB, those
> rows are compared again a fixed value and then they are inserted to the DB
> if
> the comparison is ok.
> I would like to do all that inside the DB (a SP) but I don't know how, so
> I
> created a WebService to do that, my point is that I'm not inserting row by
> row instead I hold the rows in a Dataset (memory) and later I make one big
> insert to the DB.
> My question is should I leave that code like this or should go to the DB
> in
> order to insert row by row to gain some performance?
> Can you think a better way to do that, because that is the heart of my app
> and I want to minimize the time it takes.
> Thnks.
> --
> Kenny M.

About loop and cells

How do i do a loop to insert excel records to new excel worksheet as i onli want a certain of the columns from the existince excel. For the existince excel mi got record of column A - M then i just need to copy data of A - L then copy from the same existince column H data to the new excel worksheet. For those who know pls put up codes for me so tat i can have a better understanding over it . THankzAre you sure you have the right forum?|||oh so sorry coz i need to do this first then i can have a standard excel to push it into mssql database. SO i need to solve this situation before i can proceed. If i really post wrong so sorry as i am new .

About Check Constraint

Dear all,
I know Check Constraint can help stop some unwanted data pattern insert into
the field.
It can also return a system message about this.
How can I modify the check constraint return system message to a more user
friendly message in SQL Server?
IvanOn 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Dear all,
> I know Check Constraint can help stop some unwanted data pattern insert into
> the field.
> It can also return a system message about this.
> How can I modify the check constraint return system message to a more user
> friendly message in SQL Server?
> Ivan
Use a TRY / CATCH block in your procedure to catch the error and
handle it accordingly.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Are TRY & CATCH SQL command?
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
> Use a TRY / CATCH block in your procedure to catch the error and
> handle it accordingly.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> Are TRY & CATCH SQL command?
Yes, introduced in SQL Server 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ivan" <ivan@.microsoft.com> wrote in message news:eq1CIi7rHHA.1848@.TK2MSFTNGP03.phx.gbl...
> Are TRY & CATCH SQL command?
> Ivan
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
>> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
>> Dear all,
>> I know Check Constraint can help stop some unwanted data pattern insert
>> into
>> the field.
>> It can also return a system message about this.
>> How can I modify the check constraint return system message to a more
>> user
>> friendly message in SQL Server?
>> Ivan
>> Use a TRY / CATCH block in your procedure to catch the error and
>> handle it accordingly.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>

About Check Constraint

Dear all,
I know Check Constraint can help stop some unwanted data pattern insert into
the field.
It can also return a system message about this.
How can I modify the check constraint return system message to a more user
friendly message in SQL Server?
Ivan
On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Dear all,
> I know Check Constraint can help stop some unwanted data pattern insert into
> the field.
> It can also return a system message about this.
> How can I modify the check constraint return system message to a more user
> friendly message in SQL Server?
> Ivan
Use a TRY / CATCH block in your procedure to catch the error and
handle it accordingly.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Are TRY & CATCH SQL command?
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
??:1181802475.387039.108510@.d30g2000prg.googl egroups.com...
> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Use a TRY / CATCH block in your procedure to catch the error and
> handle it accordingly.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||> Are TRY & CATCH SQL command?
Yes, introduced in SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ivan" <ivan@.microsoft.com> wrote in message news:eq1CIi7rHHA.1848@.TK2MSFTNGP03.phx.gbl...
> Are TRY & CATCH SQL command?
> Ivan
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> ??:1181802475.387039.108510@.d30g2000prg.googl egroups.com...
>

About Check Constraint

Dear all,
I know Check Constraint can help stop some unwanted data pattern insert into
the field.
It can also return a system message about this.
How can I modify the check constraint return system message to a more user
friendly message in SQL Server?
IvanOn 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Dear all,
> I know Check Constraint can help stop some unwanted data pattern insert in
to
> the field.
> It can also return a system message about this.
> How can I modify the check constraint return system message to a more user
> friendly message in SQL Server?
> Ivan
Use a TRY / CATCH block in your procedure to catch the error and
handle it accordingly.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Are TRY & CATCH SQL command?
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
> On 14 Jun, 05:33, "Ivan" <i...@.microsoft.com> wrote:
> Use a TRY / CATCH block in your procedure to catch the error and
> handle it accordingly.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> Are TRY & CATCH SQL command?
Yes, introduced in SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ivan" <ivan@.microsoft.com> wrote in message news:eq1CIi7rHHA.1848@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Are TRY & CATCH SQL command?
> Ivan
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1181802475.387039.108510@.d30g2000prg.googlegroups.com...
>[/vbcol]