intermittent performance problems while insertig records

I have a SP that I use to insert records in a table. This SP is called
hundreds of times per minute.

Most inserts complete very fast. And the profiler data is as follows:

CPU: 0
Reads: 10
Writes: 1
Duration: varies from 1 to 30

But once in a while the insert SP seems to stall and takes a very long
time. Here's the info returned by profiles in this case:

CPU: 0
Reads: 10
Writes: 1
Duration: can vary from 6000 to 60000

Note that the CPU, reads, writes remain the same. But the duration of
the SP increases. What could be the reason for this?? The SP
eventually completes in all cases - its just that they seem to take a
very long time sometimes??

What areas should I investigate??

Thanks in advance,

DKDK (dk@.realmagnet.com) writes:
> Most inserts complete very fast. And the profiler data is as follows:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: varies from 1 to 30
> But once in a while the insert SP seems to stall and takes a very long
> time. Here's the info returned by profiles in this case:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: can vary from 6000 to 60000
> Note that the CPU, reads, writes remain the same. But the duration of
> the SP increases. What could be the reason for this?? The SP
> eventually completes in all cases - its just that they seem to take a
> very long time sometimes??

The most likely cause is blocking. That is, another process accesses
data from the table, which prevents the INSERT operation to continue.
It could be that this access operation is poorly written, and does not
make use of indexes.

Another possible cause is autogrow. This is more likely to be the cause
if the database is small. Say that you started with 10 MB database. The
default is to autogrow with 10%. You will get frequent autogrows. On
the other hand, if the database is 10 GB in size, the autogrows will
not appear equally often. The remedy here is to pre-grow to a determined
size.

Rather than the data file autogrowing, it could be the transaction
log that autogrows.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||you should look at some options like auto shrink and auto growth wich may
use a lot of I/O; you should shrink manually during offline hours and give
auto growth a sufficient value for a week/month of insert activity.

Maj

"DK" <dk@.realmagnet.com> wrote in message
news:14f9b5f4.0309091151.2c332581@.posting.google.c om...
> I have a SP that I use to insert records in a table. This SP is called
> hundreds of times per minute.
> Most inserts complete very fast. And the profiler data is as follows:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: varies from 1 to 30
> But once in a while the insert SP seems to stall and takes a very long
> time. Here's the info returned by profiles in this case:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: can vary from 6000 to 60000
> Note that the CPU, reads, writes remain the same. But the duration of
> the SP increases. What could be the reason for this?? The SP
> eventually completes in all cases - its just that they seem to take a
> very long time sometimes??
> What areas should I investigate??
> Thanks in advance,
> DK|||Thanks for your replies. I am sure auto-grow is not causing this -
because the datafile size is almost 10Gb and the growth is set to 25%.
And I have noticed this issue quite frequently - sometimes 4-5 times in
a day.

Blocking could be an issue - how can I find if "blocking" is indeed the
reason - does profiler have a counter that indicates "blocking"??

Thanks

DK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||DK (netedk1@.yahoo.com) writes:
> Blocking could be an issue - how can I find if "blocking" is indeed the
> reason - does profiler have a counter that indicates "blocking"??

Hm, don't remember off hand if you can track blocking in Profiler.
Look in Books Online under Administrating SQL Server/Monitoriing Server
Performance. There is a very good description of what events and what
data you can catch with Profiler.

The simplest way to see blocking is to run sp_who, and look for non-zero
values in the Blk column. But your blocking scenarios appear to fairly
short, a couples of seconds, so you would have to run it frequently to
see any.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Blocking will manifest itself as long duration for the 'Lock: Acquired'
event. You can filter these events based on your target threshold (e.g.
Duration >= 5000). It may be helpful to include the ObjectID column in
the trace.

The sp_who (or sp_who2) technique mentioned by Erland is handy to
monitor and analyze blocking while it is occurring. You can also use
sp_lock to help identify the contended resource.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DK" <netedk1@.yahoo.com> wrote in message
news:3f5e968e$0$62084$75868355@.news.frii.net...
> Thanks for your replies. I am sure auto-grow is not causing this -
> because the datafile size is almost 10Gb and the growth is set to 25%.
> And I have noticed this issue quite frequently - sometimes 4-5 times
in
> a day.
> Blocking could be an issue - how can I find if "blocking" is indeed
the
> reason - does profiler have a counter that indicates "blocking"??
> Thanks
> DK
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for all your suggestions - I have been trying out your
recommendations - but looks like blocking is not the issue here. I tried
running sp_who/ sp_who2 while the inserts seemed to have stuck (and was
taking a long time) - but there was no blocking.

I am wondering if it could be something to so with the network
connection or the database connection that my app. server makes with the
db server. Here's some more info on what exactly is happening:

In my app. I have 3 threads that could be inserting records in this same
table.

Thread 1: loop through 10000 times and insert records in TableA

Thread 2: loop through 5000 times and insert records in TableA

Thread 3: loop through 20000 times and insert records in TableA

All these 3 threads may be running simultaneously. And it often happens
that one of these threads get stuck while the other keeps writing. So
say for example Thread 1 is has written 1003 records; the 1004th record
may take almost 10-60 seconds. And thread2 keeps writing. Thread1
eventually starts again; but again gets stuck at some other number.

While this is happening, I have observed that once a particular thread
gets stuck - its always that thread that keeps having issues. While the
other threads keep going on. This leads me to suspect that it could be
the database connection. But am not sure how I can confirm this? Or if
this could be the case at all? Any ideas how I can go about
investigating this??

Thanks for all your help once again...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Do you have a separate database connection for each thread? How many
CPUs on the database and app servers?

You might examine master..sysprocesses info while a thread is stalled to
see if that indicates why a thread is waiting.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DK" <netedk1@.yahoo.com> wrote in message
news:3f608771$0$62077$75868355@.news.frii.net...
> Thanks for all your suggestions - I have been trying out your
> recommendations - but looks like blocking is not the issue here. I
tried
> running sp_who/ sp_who2 while the inserts seemed to have stuck (and
was
> taking a long time) - but there was no blocking.
> I am wondering if it could be something to so with the network
> connection or the database connection that my app. server makes with
the
> db server. Here's some more info on what exactly is happening:
> In my app. I have 3 threads that could be inserting records in this
same
> table.
> Thread 1: loop through 10000 times and insert records in TableA
> Thread 2: loop through 5000 times and insert records in TableA
> Thread 3: loop through 20000 times and insert records in TableA
> All these 3 threads may be running simultaneously. And it often
happens
> that one of these threads get stuck while the other keeps writing. So
> say for example Thread 1 is has written 1003 records; the 1004th
record
> may take almost 10-60 seconds. And thread2 keeps writing. Thread1
> eventually starts again; but again gets stuck at some other number.
> While this is happening, I have observed that once a particular thread
> gets stuck - its always that thread that keeps having issues. While
the
> other threads keep going on. This leads me to suspect that it could be
> the database connection. But am not sure how I can confirm this? Or if
> this could be the case at all? Any ideas how I can go about
> investigating this??
> Thanks for all your help once again...
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||DK (netedk1@.yahoo.com) writes:
> I am wondering if it could be something to so with the network
> connection or the database connection that my app. server makes with the
> db server. Here's some more info on what exactly is happening:
> In my app. I have 3 threads that could be inserting records in this same
> table.
> Thread 1: loop through 10000 times and insert records in TableA
> Thread 2: loop through 5000 times and insert records in TableA
> Thread 3: loop through 20000 times and insert records in TableA
> All these 3 threads may be running simultaneously. And it often happens
> that one of these threads get stuck while the other keeps writing. So
> say for example Thread 1 is has written 1003 records; the 1004th record
> may take almost 10-60 seconds. And thread2 keeps writing. Thread1
> eventually starts again; but again gets stuck at some other number.

I have to admit that at this point I am completely stumped. If it is
not blocking, nor autogrow, then I can't think of anything obvious.
But here are some ideas how to improve your application, and thus
remove the problem.

1) Issue SET NOCOUNT ON when you connect.
2) Use the bulk-copy interface instead.
3) Form an XML document of all rows to insert, and then send down
all the data to a stored procedure that unpacks the XML into a
result set with OPENXML. This can give a tremendous performance
boost.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
RaphaelConfirm if there are any triggers on the table that might be causing this.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Also confirm if there are any constraints.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Can you show the code? Where are you getting the 0 rows affected? By the
way there is no need to wrap a single Insert in a transaction as it is
ATOMIC on it's own.
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
> Can you show the code? Where are you getting the 0 rows affected? By the
> way there is no need to wrap a single Insert in a transaction as it is
> ATOMIC on it's own.
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>|||Well you still didn't say how you determined the result was 0. I see no
code to indicate where you are collecting @.@.ROWCOUNT or returning any value.
Why are you using Numeric(18,0) when the value is obviously an Integer? You
are using 9 bytes to store what you can in 4 bytes with an Integer. You
also do not specifyt he size in the parameter of the sp. Always specify the
size when you declare a DataType or you can get unexpected results. Are you
sure there are no INSERT triggers on this table?
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
> 1) Create TABLE COMMAND
> CREATE TABLE [cm].[BILHETE] (
> [IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [IDHOTEL] [numeric](18, 0) NULL ,
> [DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
> 2) Insert COMMAND executed by the application. Only an example, the error
> is
> intermittent and random.
> INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
> ('1450290000001010022161313', 1, getDate());
> 3) I've already tried with a Stored Procedure, but accurs the same
> problem.
> After many times the SP returns "0 (zero) rows affected". Do not insert
> nothing and no exceptions are generated.
> CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
> Numeric ) AS
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> 3) The table has the follow DELETE TRIGGER, that saves the deleteds
> records
> in another table _BILHETE with the same structure:
> CREATE TRIGGER tdBilhete
> ON BILHETE
> FOR DELETE AS
> DECLARE @.STRBILHETE VARCHAR(120)
> DECLARE @.IDHOTEL NUMERIC
> SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
> SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
> INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.STRBILHETE,@.IDHOTEL,getdate());
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>|||The result was determined ZERO trought the ROWSAFFECTED property from DB
connection class, that returns the @.@.ROWCOUNT value. So i verified through a
select tha the record was no inserted.
The SP was an alternative, but even in the application the INSERT generated
the same error.
There's no INSERT TRIGGER on this table.
Thanks Andrew i really appreciated your dedication with my problem...
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Well you still didn't say how you determined the result was 0. I see no
> code to indicate where you are collecting @.@.ROWCOUNT or returning any
> value. Why are you using Numeric(18,0) when the value is obviously an
> Integer? You are using 9 bytes to store what you can in 4 bytes with an
> Integer. You also do not specifyt he size in the parameter of the sp.
> Always specify the size when you declare a DataType or you can get
> unexpected results. Are you sure there are no INSERT triggers on this
> table?
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||Are there any unique indexes on the table other than the PK constraint? If
you are positive there is no other triggers that can affect this then there
must be an error of some type generated when it fails to insert. In the sp
try this to see if it makes any difference.
DECLARE @.Error INT, @.Rows INT
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
IF @.Error <> 0
RAISERROR(@.Error, 16,1)
RETURN @.Rows
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
> The result was determined ZERO trought the ROWSAFFECTED property from DB
> connection class, that returns the @.@.ROWCOUNT value. So i verified through
> a select tha the record was no inserted.
> The SP was an alternative, but even in the application the INSERT
> generated the same error.
> There's no INSERT TRIGGER on this table.
> Thanks Andrew i really appreciated your dedication with my problem...
> Raphael
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
>|||Good idea Andrews,
I will test this and after i post here the result.
tks,
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:O$TB20s7FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Are there any unique indexes on the table other than the PK constraint? If
> you are positive there is no other triggers that can affect this then
> there must be an error of some type generated when it fails to insert. In
> the sp try this to see if it makes any difference.
> DECLARE @.Error INT, @.Rows INT
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
> IF @.Error <> 0
> RAISERROR(@.Error, 16,1)
> RETURN @.Rows
>
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
>

Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
Raphael
Please post insert statement and create table. Does this table have a
trigger (instead off or something like that)? How do you insert? Through
stored procedure or not?
MC
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>
>
|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"MC" <marko_culo#@.#yahoo#.#com#> escreveu na mensagem
news:OuB80cq7FHA.444@.TK2MSFTNGP11.phx.gbl...
> Please post insert statement and create table. Does this table have a
> trigger (instead off or something like that)? How do you insert? Through
> stored procedure or not?
> MC
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>
sql

Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
RaphaelPlease post insert statement and create table. Does this table have a
trigger (instead off or something like that)? How do you insert? Through
stored procedure or not?
MC
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>
>|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"MC" <marko_culo#@.#yahoo#.#com#> escreveu na mensagem
news:OuB80cq7FHA.444@.TK2MSFTNGP11.phx.gbl...
> Please post insert statement and create table. Does this table have a
> trigger (instead off or something like that)? How do you insert? Through
> stored procedure or not?
> MC
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>

Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
RaphaelPlease post insert statement and create table. Does this table have a
trigger (instead off or something like that)? How do you insert? Through
stored procedure or not?
MC
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>
>|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"MC" <marko_culo#@.#yahoo#.#com#> escreveu na mensagem
news:OuB80cq7FHA.444@.TK2MSFTNGP11.phx.gbl...
> Please post insert statement and create table. Does this table have a
> trigger (instead off or something like that)? How do you insert? Through
> stored procedure or not?
> MC
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:uD$ygZq7FHA.736@.TK2MSFTNGP09.phx.gbl...
>>I have a sequence of 3 operations, and its repeated many times by an
>>aplication (timer loop)
>> 1) Open a transaction
>> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
>> auto-increment identificator and some fields numbers and chars.
>> 3) Comitt the transaction
>> This proccess is executed normaly during many days, but many times this
>> command returns "0 rows affected" to the application, but with no
>> exception, just returns "0 rows affected".
>> The version is 2000 with all SP.
>> Thanks
>> Raphael
>>
>

Interface for Visual C++ in VS 2005?

I've been tasked to understand and develop an easy interface to query (insert, select, update, etc) a sql server compact database on an x86 win ce 5 machine. I'm using VS 2005 and have all the necessary SDK's installed. The problem is I can't find any good tutorials or documentation on sql server compact coding in C++ (only in C# and VB). Do y'all have any suggestions on where I can get started to learn the basic c++ sql interface coding?

Thanks!
Jeff

Edit: I'd like to note that I can run the sample northwind project on a win ce 5 x86 emulator, so it's not the setup that I need help with, but the actual coding.

Here are a couple of good places where you can start:

http://www.codeproject.com/ce/#Database

http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.perform(list_folder)=&

|||Thanks for the articles. I like what I see with the ATL OLE DB Consumer Templates articles you wrote, but are they still valid for Visual Studio 2005 or just with eVC++ 3 and 4? I tried converting the sample projects, but I got a ton of compile errors as if the atl library was now very different than the one used in the sample project.

Thanks for any light you can shed!

Jeff
|||

Hi ,

I'am also looking for the same thing.If you get the information, please post it.My requirement is Windows Ce 5 as the mobile device and sql ce 3 and above as the backend.I have tried with the articles posted in the codeproject.com resulting with no luck. I have also tried with ADO and ATL OLE DB but resulting in tons of errors.Please help me in this regard.

|||

I am still using the same headers in VS2005. It's a bit of a kludge, but the Windows Mobile 5 SDK do not ship with the newer versions of the consumer templates (you can see these headers in the Win32 SDK). So far these have worked without issues.

|||Hi,

Thank you very much for quickly reacting to my problem.I have tried to convert a pocket pc 2003 Database application which is shipped with Sql ce 2.0 named Northwindoledb.

I have taken a win32 smart device application and copied all the files from Northwindoledb application and made the following changes .

I have removed the following files from "stdafx.h" header file

"oledb.h"
"oledberr.h"

I have included the following header file to my application.

"ssceoledb.h"

After this I have added the code right after

// Microsoft SQL Server for Windows CE 2.0 Provider (Microsoft.SQLSERVER.OLEDB.CE.2.0)
//
extern const OLEDBDECLSPEC GUID CLSID_SQLSERVERCE_2_0 = {0x76A85B2E,0x9DE0,0x4ded,{0x8E,0x69,0x4D,0xEF,0xDB,0x9C,0x09,0x17}};

in the ssceoledb.h header file.

// Microsoft SQL Server Lite for Windows 3.0 Provider (Microsoft.SQLSERVER.OLEDB.CE.3.0)
//
// {32CE2952-2585-49a6-AEFF-1732076C2945}
//
extern const OLEDBDECLSPEC GUID CLSID_SQLSERVERCE_3_0 = {0x32ce2952, 0x2585, 0x49a6, {0xae, 0xff, 0x17, 0x32, 0x7, 0x6c, 0x29, 0x45}};

and after this add the following line to the "ssceoledb.h" header file

typedef DWORD DBROWSTATUS;

And installed the sqlce 3.0 on windows CE 5.0 mobile.

The application has started running.

But question to you is how can I convert the Same project to MFC Dialog Based application.

Please help me in this regard.

Bye..

J.V.Sivaram

interesting update/insert trigger problem (null issue)

Ok so here is the issue. I am thinking I somehow have to clear the old data
after executing the trigger.
So I add a user Joe Brown with his info on a users table, a trigger fires
and dumps the duplicate data into a users-dup table (for other justifiable
purposes). Update does the same basic thing.
Works fine. Here is the problem. I then add another name. Jack Black and
his info, but he has some null values... like i don't know his address. So
now the trigger fires and all of the duplicate data is carried across to the
dup table... except where there was no data (NULL) in a field... it is
adding the last "real" data set in replace of the null. SO Jack Black has
Joe Brown's address in his field... since it was the last "not null" value
entered in that column.
Looking for ideas. Figured it is something simple, I am just missing. Like
some sort of purge call.
Below is the code I am using:
for insert:
CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
FOR INSERT
AS
insert into user_marketing (greeting, fName, lName, title, compName,
address, city, provState, fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, fk_userID)
select greeting, fName, lName, title, compName, address, city, provState,
fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, pk_userID
FROM Inserted
for update:
CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
FOR UPDATE
AS
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM user_marketing a, users b
where a.fk_userID=
(SELECT pk_userID
FROM Inserted)
Thanks!Two questions.
1 - Why do you want to do this?
2 - How can we identify the last "real" data inserted in the table?, How do
you know it is "real" and not a fake like you are trying to do?
AMB
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Hi
Triggers are executed per statement, which can update multiple rows. Using
where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
arbitrary value. You are also not relating user_marketing to users
To keep user_marketing in step try:
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM dbo.user_marketing a
JOIN Inserted b ON b.pk_userID = a.fk_userID
John
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Thanks John...
That worked. Duh...
"John Bell" wrote:
> Hi
> Triggers are executed per statement, which can update multiple rows. Using
> where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
> arbitrary value. You are also not relating user_marketing to users
> To keep user_marketing in step try:
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM dbo.user_marketing a
> JOIN Inserted b ON b.pk_userID = a.fk_userID
> John
>
> "cheezebeetle" wrote:
>

Interactive reports

Is there a way to insert reports with interavtive features in my custom web
application?
I cannot use url access provided with the SQL Reporting Services.
I need to render an OLAP Report with drill down features.I am curious about this as well -> we are integrating a portfolio of reports
into our web app, several of them are dynamic (expand / hide) report
sections.
We have a report parameter page (assembled with SOAP calls) and a seperate
page which reads parameters from url and makes the SOAP call. We are trying
to avoid direct access to the ReportServer so that users do not have "direct"
access to the Web Service.
However, when a direct report is called, the external users are prompted to
login in order to see the + / - images. When they fail, the links are broken
and they cannot toggle open the report sections because the images link
directly to the ReportSever via Url Access.
Can I inject a custom url into the + / - links so that I can redirect the
user to my web app page to call for the opened report? Is there an easier
way to do this?
"massimo" wrote:
> Is there a way to insert reports with interavtive features in my custom web
> application?
> I cannot use url access provided with the SQL Reporting Services.
> I need to render an OLAP Report with drill down features.
>
>|||If you are talking about embedding a report into your asp.net page, then yes
you can. You will find a sample application at: C:\Program Files\Microsoft
SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer
This solution, when compiled will produce the DLL file in Bin directory. You
need to add a reference to this in your project and add the component
(ReportViewer) to the tool box in VS.
Regards,
KS
"massimo" wrote:
> Is there a way to insert reports with interavtive features in my custom web
> application?
> I cannot use url access provided with the SQL Reporting Services.
> I need to render an OLAP Report with drill down features.
>
>|||Yes, I have accomplished this, but we want our reports to have toggle items
while avoiding URLAccess altogether. Is this possible?
"saleek" wrote:
> If you are talking about embedding a report into your asp.net page, then yes
> you can. You will find a sample application at: C:\Program Files\Microsoft
> SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer
> This solution, when compiled will produce the DLL file in Bin directory. You
> need to add a reference to this in your project and add the component
> (ReportViewer) to the tool box in VS.
> Regards,
> KS
> "massimo" wrote:
> > Is there a way to insert reports with interavtive features in my custom web
> > application?
> >
> > I cannot use url access provided with the SQL Reporting Services.
> > I need to render an OLAP Report with drill down features.
> >
> >
> >|||Hi,
I think it is possible. You can see a live demo of MS Reporting
Services on Internet from www.gmsbv.nl / www.reportportal.com and test
the MS Reporting Services reports with paramters by your self. You
even can transfer to OLAP reports and slice and dice.
Regards, Marco
www.gmsbv.nl
"briberry" <briberry@.discussions.microsoft.com> wrote in message news:<029E1873-67A1-4EE8-B433-B4D7F3F27C74@.microsoft.com>...
> Yes, I have accomplished this, but we want our reports to have toggle items
> while avoiding URLAccess altogether. Is this possible?
> "saleek" wrote:
> > If you are talking about embedding a report into your asp.net page, then yes
> > you can. You will find a sample application at: C:\Program Files\Microsoft
> > SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer
> >
> > This solution, when compiled will produce the DLL file in Bin directory. You
> > need to add a reference to this in your project and add the component
> > (ReportViewer) to the tool box in VS.
> >
> > Regards,
> >
> > KS
> >
> > "massimo" wrote:
> >
> > > Is there a way to insert reports with interavtive features in my custom web
> > > application?
> > >
> > > I cannot use url access provided with the SQL Reporting Services.
> > > I need to render an OLAP Report with drill down features.
> > >
> > >
> > >

Integrity with multiple commands

How can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?

Write them in a stored procedure~

Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.

In short, it's something atomic, a lot of database use some mechanism such as rolling back when a sp is terminated unexpectedly, so either none or all of your command in a sp will be executed~

|||

A stored procedure has absolutely nothing to do with it. You want to place your code within a transaction. You can do this manaully by specifying a BEGIN TRANSACTION (your statements here) and then either ROLLBACK TRANSACTION or COMMIT TRANSACTION, *OR* you can use the sqltransaction object to put multiple sqlcommands within the same transaction.

You can of course put the whole thing in a stored procedure as well for convience, but putting things in a sp won't guarantee they will either all commit or rollback.

This would be an example of what you can place within the commandtext of the sqlcommand property:

dim cmd as new sqlcommand("SET @.RetVal=0; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); IF @.@.ERROR=0 BEGIN INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2) IF @.@.ERROR=0 BEGIN COMMIT TRANSACTION SET @.RetVal=1 END END IF @.RetVal=0 ROLLBACK TRANSACTION")

cmd.Parameters.Add("@.col1",varchar).value=something

cmd.Parameters.Add("@.col2",varchar).value=something

cmd.Parameters.Add("@.retval",int).Direction=output

cmd.executenonquery()

if cmd.Parameters("@.retval").Value=0 then

' It failed

end if

You can also do it this way:

dim cmd as new sqlcommand("SET @.RetVal=0; SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2); COMMIT TRANSACTION; SET @.RetVal=1")

That should also work as well.

Integration Services: ?Table refresh (UPDATE/INSERT)“

Hello

I have a question about the new Integration Services of the MS SQL Server 2005.

Situation:
- SQL Server 2005 (standard edition)

- 2 tables with identical structure (same attributes)

- the table ?TestSource“ will be constantly extend (new records & updates).

- the table ?TestDestination“ will just be refreshed by SSIS (Data Warehouse table)

I would like to create a Integration Service, witch refreshes the table ?TestDestination“ with the data from table ?TestSource“.

Existing records (ID already exists) should be updated (UPDATE), not existing records should be created (INSERT).

I would like to use the IS Data Flow Task, because in future i won’t just copy the data. I also will use Toolbox items like ?Data Conversion“, ?Derived Column“ and so on.

Alike I won’t use an easy SQL-Query, because it would be complicated to make changes and to Log the transactions.

Just clear and refill the whole table is not possible because of performance and availability requests (large data).

Question:
How can I implement this workflow as Data Flow in a Integration Service?
Witch components from the Toolbox do I need?

Greetings

Not sure if you can use this within SSIS, but the tablediff.exe that comes with sql server 2005 is pretty slick. I wrote a c# wrapper for it and I have a config file that holds all the table names that I want to sync as well as the source and destination servers. It then generates the sql change file as well as gives output of the number of rows that are out of sync. It works really well for me especially using the C# wrapper that I wrote.

Just a thought....

Integration Services (SSIS)

Hi,
I want to insert datas from a txt-file into a sql-table.
Therefor i would use a xml-file for the structure!
How can i refer this xml-file to a measurement insertion task?
Tanks for your help and sorry for my bad english :rolleyes:http://msdn2.microsoft.com/en-us/library/bb332055.aspx & http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx fyi.

integration of 2 SQL 2005 DB Servers

Hi,

I have 2 database servers and each has same database.Program that inserts these databases randomly selects one of them to insert data.So only difference is data.

I have to generate some report using these 2 sources.System administrators reject integrating these 2 servers into one.

My question is , I want to run a query that looks the column x in table y and finds unique records in this table.But this works just in one DB.What do you recommend me to find solution that finds real unique records, i.e. looks both servers to find records.

Is there a Microsoft product that may be configured brain of different DB Servers or any other solution?

I have also memory restrictions and data is considered as more than 100 GBytes for each server.

Answers will be highly appreciated.

Thanks.

sysdamins' guys also denied you create links between these servers?|||No, link is allowed.