problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Hi,

I have developed a stand alone application that get data from excel and dumps it in local database (sql server database primary data file) in visual studio .net 2005. I had no issues while developing the application. When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

connection string i am using is

Dim objLocalDB As System.Data.SqlClient.SqlConnection

objLocalDB = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & System.AppDomain.CurrentDomain.BaseDirectory & "LFDB.mdf;Integrated Security=True;User Instance=True")

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

hi,

Manyam wrote:

When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

I can't understand if you installed SQLExpress as well, on the target computer... did you?

regards

|||SQL Server Express is a backend database although the GUI tools are more lightweight than the ones of the more bigger editions. If you want to use user instances as mentioned in the connection string, you will need to have a SQL Server Express Service in place to attach the database to the Service. SQL Server Express can be deployed within your application (even through clickonce deplyoment but at the end need to be installed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Manyam wrote:

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

SQL Express is not part of the .NET Framework, it is a completely separate product that must be installed if you are going to use it. You can add SQL Express as a prerequisite to you application installation automatically using either ClickOnce or standard Setup Projects in VS 2005, in your projects properties, click the Prerequisite button and make sure SQL Express is checked in the list. This will automatically include the SQL Express installer in your deployment and install it on the target computer if it is needed.

Mike

|||

Hi,

Thanks for your suggestion. So we cannot run vb.net application that is developed using sql server management studio without SQL EXPRESS and only .net framework.

I tried to open the link provided by you. Its not opening. I am getting page cannot be displayed.

|||

Hi,

Thanks for your valueable suggestion. So i cannot run a vb application developed using sql server management studio without SQL EXPRESS.I tried installing SQL EXPRESS in standalone system, but its giving issues.

Process followed -->

Uninstalled existing framework.

Installed .NET framework 2.0Dowloaded SQLEXPRESS and istalled in system.

Its throwing a error that .net should require some updates... What updates do we need to install to get this working.

Thanks in advance.

|||What's your OS? If WIndows XP Pro, this needs SP2 and updated security patches

Problem restoring a database

Can any of you give me a hint why we have problems with restoring a database. The database is 300 GB, divided on 4 database files in the primary filegroup and running in simple recovery mode. The restore starts and finish after few minutes saying 'Restore completed successful' but the database is in Loading mode.
Ergo, the restore is not successful. The only way we can restore the database is to drop the database and delete the files and create the database via the restore. But then it creates 4 data.MDF files and not 1 MDF and 3 NDF files and the name on the files are not the same as the original. Why?

We are running on a SQL 2000 SP4 but the backup was taken under SP3A

Regards Anna-LenaWhat does

RESTORE VERIFYONLY

This tell you...I'm thinking something not to nice...

That should be part of your back up procedures....And at 400 GB...that's a lot of data...I would also have a standby box to test the dump and restore immediatley after...how long does 400 GB take to dump?

problem pushing subscriber updates to publisher in transactional replication -

I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below

dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @.publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting

In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1

Any help would be greatly appreciatedFew suggestions:
Refer to SQLAgent log for further information.
Meanwhile, stopping and restarting SQLServerAgent may allow you to temporarily resolve the problem that you are experiencing.

Did you try to forcefully terminate any replication agents in task manager by any chance?

Finally, running the snapshot agent from the command line may allow you to determine whether it was the snapshot agent that crashed unexpectedly.|||Originally posted by Satya
Few suggestions:
Refer to SQLAgent log for further information.
Meanwhile, stopping and restarting SQLServerAgent may allow you to temporarily resolve the problem that you are experiencing.

Did you try to forcefully terminate any replication agents in task manager by any chance?

Finally, running the snapshot agent from the command line may allow you to determine whether it was the snapshot agent that crashed unexpectedly.

The SQLAgent log did not have any errors in it

Stopping and restarting SQLServerAgent did not allow me to temporarily resolve this problem. I still get the same error.

I did not forcefully terminate any replication agents in task manager.

Should I run the snapshot agent from the command line or the queue agent since the queue agent is what is failing?

I tried running the queue agent from the command line and it failed with the same error as above|||Check whether the KBA [http://support.microsoft.com/default.aspx?scid=kb;EN-US;294970] is any good to you.|||I don't think that is my problem. The only way I have been able to get the queue agent to work again is to either drop and readd the subscription or reinitialize the subscription. Of course when I do this it wipes out any updates that have been done to the subscriber.

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GO
Do not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
============================= CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:
> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity(
)
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists
in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccuren
t,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>