Field not sorting in ascending order

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

That's because your field is not numeric, if it were numeric, you would expect 61,100,200

but, with text, it looks at MP with a '1' and sees it first, then, MP with a '2' and sees it next - - naturally 6 is after 2 - - but that's the reason.

|||

It is possible to get text to behave a bit like numbers.

If you had stored this instead, it would sort correctly:

MP061, MP100, MP200

This example presumes that the numerical component will always be no more than 3 characters and all 3 postiions are identified for each record (even if they are zero). In general, it's better not to try to sort alpha-numeric data in a numerical sort order.

|||If all of your records have a 2-character prefix in that column, andthere are only numeric characters that follow, you can use thisapproach to solve your sorting problem:

SELECT
someColumns
FROM
someTable
ORDER BY
CAST(SUBSTRING(mpColumn,3,99) AS integer),
mpColumn

fetching data in a datatable

hello, i'm using asp.net 2 with VB, i have a table in my db named 'exams' and i have 6 columns in this table one for question number and one for the question and 3 columns for 3 answers and a last column for the right answer, now in my page i want to show 7 questions and each question has 3 answers that i can choose between them i want the question appear in datatable and the answers in radiolist i have a little code for that but it shows an error it says "no row at position 1" so hope u guys can help :

Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnetdb.mdf;Integrated Security=True;User Instance=True")
Dim a As New ArrayList(8)
Dim i As New Integer
i = 0
Dim d As New DataSet()
Dim cmd As New SqlDataAdapter("select top(7) question from exams", con)
cmd.Fill(d, "exams")
a.Add(d.Tables.Item(0, 1))
i = i + 1
Dim ta As New DataTable
ta.Rows(i).Item(0).text = d.Tables(0).Rows(0)(i)

and by the way this code is for the questions i still dont have the code for the answers that will appear in a radiolist hope u can help .thanksnobody can help?!!!!|||Are you sure the sql query did return result?
This error always occurs if you specify an index that is out of range.sql

federated query architecture advice

I'm involved in a project building a federated query app. There is an ASP.NET web app talking to a central SQL Server 2005 db. Users input query parameters to the web ap, the request is submitted as a stored procedure to a central db, and from there it is distributed to the selected remote SQL Server 2005 db servers with similar but not necessarily identical schemas where the actual data resides.

We must wait for all servers to execute the query and return the results, then aggregate those query results and present them to the web app user.

The question is, what is the best way to design this with Server 2005? We need to be able to initiate multiple concurrent queries, then wait (up to some max timeout value) for all responses to return. What way(s) do I have with SQL Server 2005 to (1) initiate multiple queries, and (2) wait on the events (queries) to complete, up to some max timeout value?

I've looked at Service Broker some but that (maybe) seems like more than we need because all we're doing are queries. The tricky part seems to be initiating concurrent queries and waiting for all responses. Any advice or comments are appreciated.

Using SQL Server 2005 and ADO.NET 2.0 will enable you to do async queries / commands to the database. THis might be the best solution for you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I was hoping for a single stored procedure (sp) with a parameter naming the data sources to be queried. The sp would start multiple parallel queries, then consolidate the results into a single result set to return to the caller. Any ideas along these lines?|||

Just to get some understanding why do need to use federated searches. Are you running into scale problems?

The one solution is to use partitioned views, this enables the Query Optimiser to figure out which servers to query.

|||

We have multiple SQL Server databases with essentially identical schemas. These databases are located remotely from our central server (assuming we'll connect via vpn or...?). The idea is for a stored procedure call to be made to the central server that will in turn query the remote servers, with the consolidate / federated result set from all servers involved returned to the caller as a single result set.

Looks like I need to look into partitioned views; I presume this requires linked servers? If so, I've heard about problems with links staying "up" over extended periods when the remote servers are not connected to a LAN - any feedback on this question?

|||

Federation is for searching across multiple servers in a single location (in my opinion) not bringing remote data together.

Based on what you have said I believe you should be looking at a replication model. Each of your sites replicates data to the central point. The queries at the central point are then on local copies of the data.

Doing distributed queries when you have a poor connection is just not practical. Replication can handle this level of connectivity.

If you have Site A, Site B and SIte C and you need to have users in all sites seeing data from the other sites, you probably want to look at peer to perr transactional replication.

|||

Normal replication is not an option. Here's the scenario: we have multiple heterogeneous "source" systems (Oracle, Informix, SQL Server, etc.), each unique and different. Co-located with each source system is a SQL Server db. On a daily basis, the relevant tables from the source systems (which can be several GB's in total) will be copied into equivalent SQL Server tables using SSIS. (This is why replication to a central db is not an option; take this as a given, trust me). From the source schema, the data will be transformed (again via SSIS) into a "provider" schema on the co-located server. These provider schemas will be similar but not identical. We want to query and aggregate results from the provider schemas.

For example, if we have 4 sites (A, B, C, and D), and the user wants to query sites A and C, we want to fire-off parallel queries at site A nd site C. The rowset structure from the two sites will be identical, but the queries that produce them will not necessarily be identical. The final step is to combine the result sets from sites A and C and return the combined rowset to the stored procedure caller.

Once again, the co-located SQL Server boxes will be accessed over the Internet (via vpn?).

|||When you say trust me are you referring to office politics or are you referring to a technical problem? It appears (to me) that the co-located Sql Server databases (sources) could replicate to a single SQL Server database (target) from which querying could be performed. The target database could define a field to show the source of the data co-located Sql Server databases.

Faulty insert into sql express database

Hello All,

I have an InfoPath form that submits data to a .NET webservice. The webservice parses the infopath form and inserts the data into a database. The database is SQL Server EXPRESS.

I have however run into some problems. The InfoPath form is within SharePoint and I noticed that when I submit the data, it only shows up in the database as a new record 9 out of 10 times. That is for every 10 submissions of the form, there are only about 9 new records in the database. The ID field (an autoincrement field) also skips a number indicating the the transaction may have failed.

However, when I test the form on my local PC, it inserts a new record for every submission.

The webservice and database are on a remote server not my local.

Does anyone have any ideas? Could it be a deficiency of SQL Server Express?

Any ideas, answers or suggestions would be much appreciated.

Hi Hugo,

I would suggest looking at the log files on the remote server where SQL Express is installed to see if there is any information about the failure there. I doubt any failure is the sole responsibility of SQL Express, it is probably a combination of issues from many sources.

Mike

Fastest method for Inserting 1 million records into SQL Database

I am reading a text file and modifing the data to match fields in a SQL 2000 Database then inserting the record in. I am using vb.net and have tried various methods but all are to slow. I would appreciate any help anybody could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.

Fastest method for Inserting 1 million records into SQL Database

I am reading a text file and modifing the data to match fields in a SQL 2000
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText)
See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText)
>
|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest S
P
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying proble
m
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter&
#91;]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[
]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

False error when trying to return data in datagrid

VB.NET 2003 / SQLS2K

The Stored Procedure returns records within Query Analyzer.
But when the Stored Procedure is called by ADO.NET ~ it produced the
following error message.

--------
Exception Message: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.
--------

--------
Exception Source: System.Data
--------

If I click OK past the error messages I will get data filling the
datagrid. However not as I would like to see it.

Even though it returns the proper data rows and includes all the
columns I asked for, it also returns plenty of columns I didn't ask for
(all the columns of the main table) and all those columns are filled
with "null"

In addition each row header contains a red exclaimation mark whch when
hovered over reads;

"Column 'cmEditedBy' does not allow DBNull.Values."

An interesting thing about this column 'cmEditedBy' is that there is
noting wrong with it and all rows for that column contain data.

I believe this error is a mistake! But it probably indicates some other
problem. How should I track its cause?

M O R E ...
Below is the code in the data layer, the stored procedure, and the data
returned within query analyzer.

\\
'DataAdapter
Friend daView041CmptCyln As New SqlDataAdapter

'SqlCommand
Private daView041CmptCyln_CmdSel As New SqlCommand

'Add the command
daView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel

'Select
With daView041CmptCyln_CmdSel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_View_041Cmpt_ByJobCyln"
.Connection = sqlConn
With daView041CmptCyln_CmdSel.Parameters
.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.Int, _
4, ParameterDirection.ReturnValue, False, CType(0,
Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))
'Criteria
.Add("@.fkJob", SqlDbType.Text).Value = _
"48c64a55-874d-40d0-addc-7245f5d9c118"
'.Add("@.fkJob", SqlDbType.Text).Value = f050View.jobID
End With
End With
//

\\
ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln
(@.fkJob char(36))
AS SET NOCOUNT ON;

SELECT
JobNumber,
DeviceName,
ComponentName,

Description,
Quan,
Bore,
Stroke,
Rod,
Seconds,
CylPSI,
PosA,
PosB,
PosC,
PosD,
PosE,
HomeIsRet,
RetIsRetrac,
POChecks,
Regulated,
FlowControl,
PortSize,
LoadMass

FROM tbl040cmpt
INNER JOIN tbl030Devi ON fkDevice = pkDeviceId
INNER JOIN tbl020Proc ON fkProcess = pkProcessId
INNER JOIN tbl010Job ON fkJob = pkjobId
INNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeId
INNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeId
INNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeId

WHERE
(fkJob = @.fkJob)
--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'
AND fkComponentType = 2

GO
//

(note - columns are wrapped)
\\
F1111Clip DriverCylinderClip Driver_2 - Top -
Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_222.1002.0001.0001.234NULL11000110011/8
NPTNULL
F1111Clip
DriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4
NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_122.1002.0001.0001.000NULL01000110011/8
NPTNULL
F1111DegateCylinderDegate 1 -
Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULL
F1111Clip DriverCylinderClip Driver 1 -
Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL
//dbuchanan (dbuchanan52@.hotmail.com) writes:
> VB.NET 2003 / SQLS2K
> The Stored Procedure returns records within Query Analyzer.
> But when the Stored Procedure is called by ADO.NET ~ it produced the
> following error message.
> --------
> Exception Message: Failed to enable constraints. One or more rows
> contain values violating non-null, unique, or foreign-key constraints.
> --------

Nah, it sounds as if that message is produced by .Net Framework. The
stored procedure pleads innocense.

> Even though it returns the proper data rows and includes all the
> columns I asked for, it also returns plenty of columns I didn't ask for
> (all the columns of the main table) and all those columns are filled
> with "null"
> In addition each row header contains a red exclaimation mark whch when
> hovered over reads;
> "Column 'cmEditedBy' does not allow DBNull.Values."

Well, that column is not in the result set, so obviously when you try
to populated the DataSet, NULL values is all you get. And apparently
they are not permitted.

I don't have that much experience of ADO .Net, but it sounds to me that
you have run some wizard that has constructed your dataset, and you then
have not been careful which columns to include. (Personally, if I were
to work with data sets, I would probably construct them manually.) Or is
there some thought behind of including columns that are not reported by the
query?

While not relevant to your problem, permit me to point an issue of style
with your query: you table includes six tables, no column is prefixed
with any alias (or the table name). This makes it very difficult for
anyone who looks at query to tell which table, the columns are coming
from. This also mean that if the DBA adds, say, "Description" to one
more table, the procedure will no longer compile because that column name
is now ambiguous.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
__________________________________________________ _______________
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections
........
__________________________________________________ _______________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.
Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> ........
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Not as far as I'm aware. It's certainly not referenced in the
connection string?
|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegr oups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> .......
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris
|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
________________________________________
_________________________
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections
.......
________________________________________
_________________________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> ........
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Not as far as I'm aware. It's certainly not referenced in the
connection string?|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/0...onnected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegroups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections
> .......
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...

failure to install SQL Agent for SQL Express

I just tried to install the file:

D:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe SQLAgent.exe by running it from a command prompt.

and got an error that the system could not find the file or one of its components. It is a farily new install. I do not think I've lost any files any time.

What is the problem?

Thanks.

SQL Express does not include SQL Agent, you need to purchase at least SQL Server Workgroup to get that component.

Mike

Failure to create a Report Project within Visual Studio 2003

I'm having a problem creating a Report Project within Visual Studio. I have
VS.NET 2003 installed and I had then installed Sql Reporting Services with
the latest service pack. If I try and then create a reporting project within
a pre-existing solution, it gives me the following error.
Class already exists.
I looked at the knowledge base and it seems that this behaviour is caused by
a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
add-in manager and tested again. The problem still exists.
Any other ideas on what I should try?
Thanks,
Darren.On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>I'm having a problem creating a Report Project within Visual Studio. I have
>VS.NET 2003 installed and I had then installed Sql Reporting Services with
>the latest service pack. If I try and then create a reporting project within
>a pre-existing solution, it gives me the following error.
>Class already exists.
>I looked at the knowledge base and it seems that this behaviour is caused by
>a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>add-in manager and tested again. The problem still exists.
>Any other ideas on what I should try?
>Thanks,
>Darren.
Darren,
If you try to create a new project from the File menu (choose the
Wizard option for speed) does that create a working report or not?
Andrew Watt
MVP - InfoPath|||Andrew,
Just tried creating a new report project from the file menu and I still get
the same "Class already exists" message. If I look in my local directory
where I'm trying to create the new project, a folder is created there with a
virtually empty .rptproj file with the following contents.
<?xml version="1.0"?>
<Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
<DataSources />
<Reports />
</Project>
I can't however, see the project in the solution explorer and it doesn't
seem to be added to the solution file. Any other suggestions?
Thanks,
Darren.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
> <dmpenner@.discussions.microsoft.com> wrote:
> >I'm having a problem creating a Report Project within Visual Studio. I have
> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
> >the latest service pack. If I try and then create a reporting project within
> >a pre-existing solution, it gives me the following error.
> >
> >Class already exists.
> >
> >I looked at the knowledge base and it seems that this behaviour is caused by
> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
> >add-in manager and tested again. The problem still exists.
> >
> >Any other ideas on what I should try?
> >
> >Thanks,
> >Darren.
> Darren,
> If you try to create a new project from the File menu (choose the
> Wizard option for speed) does that create a working report or not?
> Andrew Watt
> MVP - InfoPath
>|||Darren,
This isn't something I have seen before so the following questions are
simply trying to identify more clearly your specific
setup/circumstances.
Can you describe your setup in more detail? Which add-ins do you have?
When did you disable them?
Have you ever (I assume not, but want to be clear) created an RS
report successfully on that machine?
Apart from RS is Visual Studio working normally?
Andrew Watt
MVP - InfoPath
On Thu, 7 Apr 2005 08:55:03 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>Andrew,
>Just tried creating a new report project from the file menu and I still get
>the same "Class already exists" message. If I look in my local directory
>where I'm trying to create the new project, a folder is created there with a
>virtually empty .rptproj file with the following contents.
><?xml version="1.0"?>
><Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
>xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
> <DataSources />
> <Reports />
></Project>
>I can't however, see the project in the solution explorer and it doesn't
>seem to be added to the solution file. Any other suggestions?
>Thanks,
>Darren.
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
>> <dmpenner@.discussions.microsoft.com> wrote:
>> >I'm having a problem creating a Report Project within Visual Studio. I have
>> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
>> >the latest service pack. If I try and then create a reporting project within
>> >a pre-existing solution, it gives me the following error.
>> >
>> >Class already exists.
>> >
>> >I looked at the knowledge base and it seems that this behaviour is caused by
>> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>> >add-in manager and tested again. The problem still exists.
>> >
>> >Any other ideas on what I should try?
>> >
>> >Thanks,
>> >Darren.
>> Darren,
>> If you try to create a new project from the File menu (choose the
>> Wizard option for speed) does that create a working report or not?
>> Andrew Watt
>> MVP - InfoPath

failure in starting the process for the user instance

High everybody,

I have a set of winforms (.NET 2.0 assemblies) ( 6 total ) that each tries to open a connection to the same Database file using the same connection string. In the connection string, UserInstance is set to true.

If each winform is started manually after opening the session on the computer, the connection to the database performs well for every winform.

If the same winforms are automatically started at the opening of the user session, the the following error is reported by each application when trying to open the connection :

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

The only way to overcome this problem is :

to stop the winforms, to stop the SQLExpress service, to kill the remaining sqlserv.exe running task, then restarting SQLExpress service, and finally manualy start the winforms.

Any help would be greatly appreciated.

Hi Henri,

When you say "a set of winforms" do you mean a single applications or six different applications?

Are all these forms being opened by the same user?

Is the database embedded in the application or is it located somewhere else? (If somewhere else, where?)

You can frequently find more detailed information about this error in the User Instance error log which is located in the user profile directories of the user who is starting the user instance: C:\Documents and Settings\<user name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. Check out this file and let us know what it says.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

I get a similar problem. I get this message anytime I try to open the database from within VB Express. I was following Lesson 08 by trying to Add a SQL Server Item Template to my project.

Here is what I found in my logs.

2006-04-08 17:11:44.62 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

2006-04-08 17:11:44.07 spid4s Starting up database 'mssqlsystemresource'.
2006-04-08 17:11:44.32 spid4s Error: 15466, Severity: 16, State: 1.
2006-04-08 17:11:44.32 spid4s An error occurred during decryption.
2006-04-08 17:11:44.50 spid4s The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

|||

Does this only happen when connecting to the user instance? Can you connect to the main instance?

Thanks
Laurentiu

|||

Hi Mike,

Thank you for your help and sorry for the late answer, but I am still on holidays. I will try to get the User Instance log at the customer site.

my application consists of six different exe that are started at the same time when opening the user session ( on a windows 2000 server). Each of these exe makes a connection to the same database file using the same connection string. When the user session is opened, there are other softwares that are automatically started which makes the CPU usage about 100% for a few minutes.

However, when each exe is started manually one after the other, the connection to the User Instance is done without error.

regards,

Henri d'Orgeval

|||

Hi Mike,

here is the error log content :

2006-04-13 10:41:30.09 Server Microsoft SQL Server 2005 - 9.00.1314.06 (Intel X86)
Sep 2 2005 21:10:31
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2006-04-13 10:41:30.09 Server (c) 2005 Microsoft Corporation.
2006-04-13 10:41:30.09 Server All rights reserved.
2006-04-13 10:41:30.09 Server Server process ID is 2468.
2006-04-13 10:41:30.09 Server Logging SQL Server messages in file 'C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log'.
2006-04-13 10:41:30.09 Server Registry startup parameters:
2006-04-13 10:41:30.09 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-13 10:41:30.09 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-13 10:41:30.09 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-13 10:41:30.09 Server Command Line Startup Parameters:
2006-04-13 10:41:30.09 Server -d C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf
2006-04-13 10:41:30.09 Server -l C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\mastlog.ldf
2006-04-13 10:41:30.09 Server -e C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
2006-04-13 10:41:30.09 Server -c
2006-04-13 10:41:30.09 Server -S SQLEXPRESS
2006-04-13 10:41:30.09 Server -s F2DAFDAF-4081-43
2006-04-13 10:41:30.09 Server -w 60
2006-04-13 10:41:30.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-13 10:41:30.09 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-13 10:41:30.46 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-04-13 10:41:30.79 Server Database mirroring has been enabled on this instance of SQL Server.
2006-04-13 10:41:30.81 spid5s Starting up database 'master'.
2006-04-13 10:41:30.90 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-13 10:41:31.04 spid5s SQL Trace ID 1 was started by login "sa".
2006-04-13 10:41:31.10 spid5s Starting up database 'mssqlsystemresource'.
2006-04-13 10:41:31.21 Server Server local connection provider is ready to accept connection on [ \\.\pipe\F2DAFDAF-4081-43\tsql\query ].
2006-04-13 10:41:31.21 spid7s Starting up database 'model'.
2006-04-13 10:41:31.21 spid5s Server name is 'IT-SHAPER1\F2DAFDAF-4081-43'. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-04-13 10:41:31.34 spid5s Starting up database 'msdb'.
2006-04-13 10:41:31.48 spid7s Clearing tempdb database.
2006-04-13 10:41:32.12 spid7s Starting up database 'tempdb'.
2006-04-13 10:41:32.18 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-04-13 10:41:32.18 spid10s The Service Broker protocol transport is disabled or not configured.
2006-04-13 10:41:32.18 spid10s The Database Mirroring protocol transport is disabled or not configured.
2006-04-13 10:41:32.34 spid10s Service Broker manager has started.
2006-04-13 10:41:32.54 spid51 Starting up database 'C:\PROGRAM FILES\W.A.I.S. TECHNOLOGY\DISTRIBUTION MANAGER\DISTRIBUTIONMANAGER.MDF'.

There are other error logs named errorN.log that contains the same content

Best regards,

Henri d'Orgeval

|||

This appears to be a successful errorlog. Can you post the errorlog that contains the errors you have extracted and posted in your earlier post?

Also, based on your description of the problem, have you considered starting the six instances of your program in succession, with a small delay between them. This might be a workaround until we determine the cause of this failure.

Thanks
Laurentiu

|||

Hi Henri,

I'm still not clear on when you're getting the error. You state that when each exe is started one after another the connection is done without error, when do you get the error?

As for the error log, could you confirm that you pulled that error log for the User Instance and not the main instance? The User Instance error log would be located at C:\Documents and Settings\<user>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS where <user> is the user that experienced the problem.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Henri,

Could you give a bit more information as requested by both Laurentiu and me? It's still not clear to use how your application is actually starting and where the error is occuring.

Have you tried the process that Laurentiu suggests and did it work for you?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi Mike and Laurentiu,

I confirm that if each exe is started one after the other, waiting a little bit between each launch, everything is right. This is the only workaround I found that works every time.

If the 6 exes are started at the same time at the opening of the user session ( ie the 6 exes are started from the Startup folder of the User Session), then each exe reports the same error which is the one I mentioned at my first post.

To reproduce the problem the following is done :

1°) the station is rebooted,

2°) as soon as the Login/Password screen appears on the screen, the operator opens the session, which starts the 6 exe at the same time.

3°) then each exe immediately tries to open the connection by attaching the same database file and using the same connection string.

About the error log file I posted :

There are many error log files in the User Instance SQL folder, but the content of each of these error log files is similar to what I posted.

I agree with you, surprisingly it does not look like an error has occured !

I am currently rewriting the software in order to have only one exe that will start 6 different threads one after the other.

Best regards

Henri d'Orgeval

|||

I actually expect that only 5 exes are failing and the 6th succeeds. Can you please confirm this?

The process of connecting to the user instance works like this (this is a high level description):

1. if the instance exists, connect to it
2. otherwise start up the instance
3. connect to the instance

I expect all exe's try step 1 and find that there's no instance, then they all try to start it up, but only one will succeed and the others will fail. This is to be expected and can be resolved in several ways:

(a) start the exe's in succession. This was already proposed and verified to be a valid solution.
(b) start one exe first and after some delay start all the others.
(c) implement code in the exe to handle the connection failure and attempt to reconnect for several times with some delay in between.

Thanks
Laurentiu