"User does not have permission to perform this action." problem

hey people

having a nightmare getting asp.net 2.0 to work with sql server 2005 express, bit of a newbie with it. trying to display a table from my sql database and every time i run the aspx table im getting this error.

 User does not have permission to performthis action.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: User does not have permission to performthis action.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.Stack Trace:[SqlException (0x80131904): User does not have permission to performthis action.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 keep getting the user permission error, even though it says on the management console that i have access to the database

any ideas?

rob 

ok the error doesnt tell you that you cant login

the problem is you already login but you cant do action.

can you look your connection string and find out what account are you using.

then check that account's right in your SQL 2005 database,

maybe the right in that account is very small

|||

check if you user have not only rights to connect to database but also right to select from tables you use or execute rights to stored procedures so check database security and your user effective permissions on database objects

Thanks

|||

(having a nightmare getting asp.net 2.0 to work with sql server 2005 express, bit of a newbie with it. trying to display a table from my sql database and every time i run the aspx table im getting this error.)

If you don't have Management Studio you can download it in the first link below and the second link covers how to add object permissions for the Asp.net account post again if you still have question. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

http://forums.asp.net/thread/1492092.aspx

"User Cancelled" Error while viewing Crystal report

Hi,
I have a Crystal Reports v10 Report that has been working fine
for the past X years. It is invoked by a VB6 app using the
Report Viewer. For some reason, sometimes when the report is
executed, it displays the message "User Cancelled" immediately
after the viewer window is opened, even though the user did not
cancel the report. None of the code has changed, neither has the
report.

Does anyone have any clue as to why this message should appear?

Very urgent, Thanks in Advance...

Regards,
AnandhSometimes I see this message when there is an issue with the print spooler on the computer where the report is being generated. Try going to Control Panel > Administrative Tools > Services and then restarting the Print Spooler service. After doing this, reboot the computer.

If this doesn't work, then it isn't the print spooler causing the problem.

Good Luck! :)

"TRUNCATE TABLE" in a sproc w/locked-down user

Hullo folks, I'm having what I assume is a fairly mundane security issue.

I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.

Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.

Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?The only people who can use 'truncate' are the table owner, sysadmin, db_owner, and db_ddladmin; so if you have a user whose permissions are severely restricted I would not think 'truncate' would work, or if it would it would be a phaff to set up.

Why not just DELETE FROM, or use a temp table to create the statistics and return them?|||This is effectively what I ended up doing after making the same permissions determination. I had my heart set on Truncate do to the nature of the process itself, but I was able to work around the restriction using DELETE FROM and a local scheduled maintenance job.|||Is there a way he can call an osql command via xp_cmdshell that connects with a trusted connection that can truncate the table ? I tried some stuff with a server link to itself, but cannot truncate table link.db.owner.table, same goes with openquery (truncate not supported).|||That would present a larger security issue then outright granting the user required permissions. I definately don't want to expose trusted command line functionality to this login.

What I have now works, it's just not exactly the way I want it to. All of my research indicates that it will just have to be good enough as I cannot get around that pesky permissions requirement for TRUNCATE.|||In the past I have allowed a user to own a single table for a similar purpose. But that was for an import table, where the data was truncated, reloaded, diced up, and distributed to other tables. No other process even knew the table was there. It gets you out of having to have db_owner rights, but there is no other way around the requirement of owning the table.|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought
That's the current working solution. The process runs a couple times a day, but same idea.

"Too many requests being handled for user" error while accessing Report Manager

Hi,
When I try to access Report Manager(http://<reportserver>/Reports) I'm
getting the following error message:
"There are currently too many requests being handled for user:
[DOMAIN]\[USER]. Please wait until the current requests have finished
processing before issuing any more."
However there are no problems when I try to access the Report
Server(http://<reportserver>/ReportServer).
Has anyone encountered a similar situation before?
Thanks.
RamkiRestarting IIS resolves the issue. Increasing the
"MaxActiveReqForOneUser" in RSReportServer.config from its default of
20 might also be an option.
Ramki

"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>> User is getting timeout errors when they try to backup a 2000 database
>> using a db backup and restore utility I wrote. The problem has just
>> started occurring w/in the last few days. I'd like to avoid driving to
>> the site, over 200-miles away, and changing the .ConnectionTimeout and
>> .CommandTimeout property of the db connection object building another
>> distribution. Is there a way to accomplish thisi via some "SET" command
>> in Enterprise Mgr.?
>> TIA
>>
>

"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>

"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIA
Timeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>
|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>

"Session" values

Is it possible to have the equivalent of a session value? What I'm wanting
to do is get the user to choose a value (item center) on the home page and
use that value to filter all the reports. Then if he wants to see the
reports for a different item center, he must go back to the home page and
choose a different one.
Even better, display the same list of item centers as a filter option at the
top of each report and default it to the currently chosen item center.
Any ideas on how to do this? I saw a post where someone asked if it was
possible to create a global variable to use throughout all reports and noone
answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
Thanks
ToddTodd,
If you create your own web application, you can certainly keep track of some
value and pass it to your reports as one of the parameters. AFAIK, there is
no built-in functionality in the ReportServer or Report Manager web apps.
Ted
"Todd Bright" wrote:
> Is it possible to have the equivalent of a session value? What I'm wanting
> to do is get the user to choose a value (item center) on the home page and
> use that value to filter all the reports. Then if he wants to see the
> reports for a different item center, he must go back to the home page and
> choose a different one.
> Even better, display the same list of item centers as a filter option at the
> top of each report and default it to the currently chosen item center.
> Any ideas on how to do this? I saw a post where someone asked if it was
> possible to create a global variable to use throughout all reports and noone
> answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
> Thanks
> Todd|||So basically each report is just a standalone report, right?
"Ted K" wrote:
> Todd,
> If you create your own web application, you can certainly keep track of some
> value and pass it to your reports as one of the parameters. AFAIK, there is
> no built-in functionality in the ReportServer or Report Manager web apps.
> Ted
> "Todd Bright" wrote:
> > Is it possible to have the equivalent of a session value? What I'm wanting
> > to do is get the user to choose a value (item center) on the home page and
> > use that value to filter all the reports. Then if he wants to see the
> > reports for a different item center, he must go back to the home page and
> > choose a different one.
> >
> > Even better, display the same list of item centers as a filter option at the
> > top of each report and default it to the currently chosen item center.
> >
> > Any ideas on how to do this? I saw a post where someone asked if it was
> > possible to create a global variable to use throughout all reports and noone
> > answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
> >
> > Thanks
> > Todd

"rsAccessDenied" Error when Anonymous User from a Virtual directory access the Reporting S

Hi,
My setup is that i have a IIS virtual directory with Anonymous user (
enable anonymous ) to access Reporting Server. Both are in same server
anyway.
I have a web page in the virtual directory with url link to the
Reprting Server.
if I grant this anonymous user with admin privilege , then it works.
Why ' what kind of privilege is needed in fact ? There are some clue
but i dont know how to intepret.
The error log from Event log is as below:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date: 18/01/2006
Time: 10:41:23
User: NT AUTHORITY\NETWORK SERVICE
Computer: SVR071
Description:
The application-specific permission settings do not grant Local
Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This
security permission can be modified using the Component Services
administrative tool.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.What is the configured identity of the 'ReportServer' service? Is it
interactive user? What is the security configuration for the
ReportServer virtual directory? What is the security configuration for
connecting to your SQL serrver?
If you're accessing the virutal directory as an anonymous user and the
RS service is configured to run as the interactive user, then the
interactive user (in this case anonymous) would need priviledges to
connect to SQL and any other necessary resources.
Andy Potter

"Replicating" SQL user accounts

Good day,
I am wanting to "replicate" user accounts between two SQL 2000 servers automatically. I.E. If I create a user on the first server, I want it automatically created on the second SQL server, if it doen't already exist.
Is there a quick and easy way to do this? If so how? I don't have to use replication, so if there is another way (I.E. DTS, etc) then that would also be great.
Thank you in advance,
Brian
no, you can't do this - the system tables can't be replicated only user
objects.
"Brian" <Brian.Louw@.sbs.siemens.co.za> wrote in message
news:17CDD729-D396-4FAC-80AB-FEE5555C530F@.microsoft.com...
> Good day,
> I am wanting to "replicate" user accounts between two SQL 2000 servers
automatically. I.E. If I create a user on the first server, I want it
automatically created on the second SQL server, if it doen't already exist.
> Is there a quick and easy way to do this? If so how? I don't have to use
replication, so if there is another way (I.E. DTS, etc) then that would also
be great.
> Thank you in advance,
> Brian
|||Brian,
there is no way to automatically do this; replication/triggers can't be used
on system tables, but you can use DTS transfer logins task, or you can
script out the logins using sp_help_revlogin (search for this proc on Google
to get info).
Regards,
Paul Ibison

"Query is too Complex"

OK, first let me say that I am no DB person.

But I have a user here who keeps getting this error
whenever she does, whatever it is she does, with
databases...

A google search takes me to various forums where
I am forced to sign up before I can read any answers.

Interesting note here is that the guy in the office next
to her can run this same query without any problems.

So, can this be a hardware issue?
If I threw more RAM at this problem would that do?

Or is this strickly a coding issue?
Why would one machine run this query ok, yet another
machine generates this error?

Any pointers that you guys could throw my way would
be greatly appreciated.
Thanks

*$Starbuck,

I am afraid you haven't provided enough information to be of help. What
error is she getting? What statement is she running? etc.

By the way, you can search the archive of the public Microsoft
SQL-Server forums on Google (without registration). Have a look at
http://groups.google.com/groups?hl=...ublic.sqlserver

Hope this helps,
Gert-Jan

Starbuck wrote:
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

--
(Please reply only to the newsgroup)|||The error message is "Query is too complex".

The Query that is being run combines 2 tables into
a table query. There is only one join.

The issue seems to be that this query runs on another
machine just fine... They have similar hardware,
and software, but it fails with a "Query is too complex"
error on her machine.

We dont feel that this is in fact a "complex" query.

Point is, I need this to run on her machine, but I am
unable to determine what the differences are.

Could this be a connection issue?

I'm really shooting in the dark here, so any suggestions
are greatly appreciated.

thanks again.
*$

On Thu, 05 Aug 2004 19:43:08 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:

>Starbuck,
>I am afraid you haven't provided enough information to be of help. What
>error is she getting? What statement is she running? etc.
>By the way, you can search the archive of the public Microsoft
>SQL-Server forums on Google (without registration). Have a look at
>http://groups.google.com/groups?hl=...ublic.sqlserver
>Hope this helps,
>Gert-Jan
>
>Starbuck wrote:
>>
>> OK, first let me say that I am no DB person.
>>
>> But I have a user here who keeps getting this error
>> whenever she does, whatever it is she does, with
>> databases...
>>
>> A google search takes me to various forums where
>> I am forced to sign up before I can read any answers.
>>
>> Interesting note here is that the guy in the office next
>> to her can run this same query without any problems.
>>
>> So, can this be a hardware issue?
>> If I threw more RAM at this problem would that do?
>>
>> Or is this strickly a coding issue?
>> Why would one machine run this query ok, yet another
>> machine generates this error?
>>
>> Any pointers that you guys could throw my way would
>> be greatly appreciated.
>> Thanks
>>
>> *$|||Starbuck (Starbuck@.BogusDomain.com) writes:
> The error message is "Query is too complex".
> The Query that is being run combines 2 tables into
> a table query. There is only one join.
> The issue seems to be that this query runs on another
> machine just fine... They have similar hardware,
> and software, but it fails with a "Query is too complex"
> error on her machine.
> We dont feel that this is in fact a "complex" query.
> Point is, I need this to run on her machine, but I am
> unable to determine what the differences are.
> Could this be a connection issue?
> I'm really shooting in the dark here, so any suggestions
> are greatly appreciated.

If you are in the dark, guess how dark we are in, who don't even know
what environment you are using, or how the query looks like.

That much I can tell, that the message "Query is too complex" does not
appear in master..sysmessages, so it is not likely to be a message from
SQL Server. To be able to assist further we need to see the query,
we need to know what environment the user gets this error in, and we
need to know which database engine you are working with. (It should be
MS SQL Server, else you are posting to the wrong newsgroup.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Starbuck" <Starbuck@.BogusDomain.com> wrote in message
news:spk4h0t52bf29a93mcpvbb1pjs7sa3hqk5@.4ax.com...
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

I've never heard of this error before - as Erland pointed out, this is not a
native MSSQL error - however some Googling suggests that it is likely to be
either an MS Access issue, or something related to some very old data access
components (the most recent exact hit for this error in Google newsgroups is
in the year 2000). For example:

http://support.microsoft.com/defaul...&NoWebContent=1

http://support.microsoft.com/defaul...&NoWebContent=1

http://www.dotnet247.com/247referen.../43/216266.aspx

http://www.google.com/search?source...ery+too+complex

Without more information, however, it's very difficult for anyone to give
any good advice. What is the database (Access, MSSQL), what is the client
(Access, in-house app, third-party app), what are the operating systems, the
servicepacks, how is the query submitted, what is the definition of the
query and tables, what difference is there between the workstations where
the query works and does not (OS, hardware) etc.

My best guess is that you are querying an MS Access database, and the "guy
in the next office" has a more recent Windows version than the user with the
error (and therefore more recent data access components), but that's purely
speculation and very likely to be wrong. If you're not using MSSQL, then as
Erland said, you will get a better response in a forum dedicated to your
database platform.

Simon

"Paging" in SP

I need to searches through records using Full Text Indexing. A search often
returns hundreds of records, and need the user to be able to page through
the result 50 at a time. I *ALSO* need the user to know the first and last
record of each page! (..On the client, i render a list of all pages in the
resultset, and add labels to each page, indicating to the user where in the
resultset the page is positioned).
I have therefor done an SP that inserts my initial search into an in-memory
table. After that i loop through all the records from my initial search, and
insert every 50th record into a different in-memory table. That allows me to
return a resultset with a) the first page of records (first 50 records), and
a table with a complete list of pages.
However, it has come to my attention that my loop through my initial
resultset is taking a lot of performance. So basically, my question is:
i have a large resultset in memory. I need to "sample" records from this,
eg. every 50th record. What would be the fastest way of doing this?
Cheers,
/Thomas.
You are using the wrong approach for this.
Here is some sample code that uses asp and getrows.
Approaches like the one you are using do not offer the best performance.
Check out
http://www.adopenstatic.com/experime...dsetpaging.asp
for more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> I need to searches through records using Full Text Indexing. A search
often
> returns hundreds of records, and need the user to be able to page through
> the result 50 at a time. I *ALSO* need the user to know the first and last
> record of each page! (..On the client, i render a list of all pages in the
> resultset, and add labels to each page, indicating to the user where in
the
> resultset the page is positioned).
> I have therefor done an SP that inserts my initial search into an
in-memory
> table. After that i loop through all the records from my initial search,
and
> insert every 50th record into a different in-memory table. That allows me
to
> return a resultset with a) the first page of records (first 50 records),
and
> a table with a complete list of pages.
> However, it has come to my attention that my loop through my initial
> resultset is taking a lot of performance. So basically, my question is:
> i have a large resultset in memory. I need to "sample" records from this,
> eg. every 50th record. What would be the fastest way of doing this?
> Cheers,
> /Thomas.
>
begin 666 citrixNew.asp
M/"4-"F]P=&EO;B!E>'!L:6-I= T*0V]N<W0@.8615<V5#;&EE;G0@./2 S#0I#
M;VYS="!A9%5S95-E<G9E<B ](#(-"D-O;G-T(&%D3W!E;D9O<G=A<F1/;FQY
M(#T@., T*0V]N<W0@.861/<&5N4W1A=&EC(#T@.,PT*0V]N<W0@.861,;V-K4F5A
M9$]N;'D@./2 Q#0I#;VYS="!A9$-M9%-T;W)E9%!R;V,@./2 T#0I#;VYS="!A
M9%!A<F%M26YP=70@./2 Q#0I#;VYS="!A9%!A<F%M4F5T=7)N5F%L=64@./2 F
M2# P,#0-"D-O;G-T(&%D5F%R0VAA<B ](#(P, T*0V]N<W0@.861);G1E9V5R
M(#T@.,PT*0V]N<W0@.:5)O=W-097)086=E(#T@.,3 -"D-O;G-T('-H;W=";&%N
M:R ]("(F;F)S<#LB#0I#;VYS="!S:&]W3G5L;" ](").54Q,(@.T*#0I$:6T@.
M:5-T87)T+"!2971U<FY686QU92P@.5V5B2&ET<U%U97)Y+"!S=')39 6%R8V@.-
M"@.T*36%I;@.T*#0I3=6(@.36%I;B@.I#0H)1&EM(')S="P@.<U -13"P@.<T-O;FY3
M=')I;F<L<U-C<FEP=$YA;64L(%-E87)C:%]0:')A<V4L(%-E87)C:%]0:')A
M<V5?5')I;6UE9 T*"6E3=&%R=#U297%U97-T+E%U97)Y4W1R:6YG*")I4W1A
M<G0B*0T*"5-E87)C:%]0:')A<V4]4F5Q=65S="Y1=65R>5-T<FEN9R@.B4V5A
M<F-H7U!H<F%S92(I#0H))U-E87)C:%]0:')A<V5?5')I;6UE9#U297!L86-E
M*%-E87)C:%]0:')A<V4L(&-H<B@.S.2DL(&-H<B@.S.2DK8VAR*#,Y*2D-"@.DG
M4V5A<F-H7U!H<F%S95]4<FEM;65D/5)E<&QA8V4H4V5A<F-H7U!H<F%S95]4
M<FEM;65D+"!C:'(H,S0I+"!C:'(H,S0I*V-H<B@.S-"DI#0H):68@.:5-T87)T
M/2(B('1H96X-"@.D):5-T87)T/3 -"@.EE;F0@.:68-"@.T*"7-#;VYN4W1R:6YG
M(#T@.(E!R;W9I9&5R/5-13$],141"+C$[4&5R<VES="!396-U<FET>2!);F9O
M/49A;'-E.T1A=&$@.4V]U<F-E/2X[=7-E<B!)1#US83MP=V0]<V4Q8W5R92,[
M26YI=&EA;"!#871A;&]G/71E>'0[(@.T*#0H)5W)I=&5486)L94AE861E<B!S
M4V-R:7!T3F%M92P@.4V5A<F-H7U!H<F%S90T*#0H)<U-C<FEP=$YA;64@./2!2
M97%U97-T+E-E<G9E<E9A<FEA8FQE<R@.B4T-225!47TY!344B*0T*"6EF(%-E
M87)C:%]0:')A<V4@./#XB(B!T:&5N#0H)"5=R:71E5&%B;&5";V1Y(&E2;W=S
M4&5R4&%G92P@.:5-T87)T+"!S4V-R:7!T3F%M92P@.<T-O;FY3=')I;F<L('-3
M44PL(%-E87)C:%]0:')A<V4-"@.EE;F0@.:68-"@.E7<FET951A8FQE1F]O=&5R
M#0H-"D5N9"!3=6(-"@.T*4W5B(%=R:71E5&%B;&5(96%D97(H<U-C<FEP=$YA
M;64L4V5A<F-H7U!H<F%S92 I#0H)<F5S<&]N<V4N=W)I=&4@.(CQ(5$U,/B(-
M"@.ER97-P;VYS92YW<FET92 B/$A%040^(@.T*"7)E<W!O;G-E+G=R:71E("(\
M345402!H='1P+65Q=6EV/2(B0V]N=&5N="U4>7!E(B(@.8V]N=&5N=#TB(G1E
M>'0O:'1M;#L@.8VAA<G-E=#UW:6YD;W=S+3$R-3(B(CXB#0H)<F5S<&]N<V4N
M=W)I=&4@.(CQ,24Y+(&AR968](B(O25,O<W1Y;&4N8W-S(B(@.='EP93TB(G1E
M>'0O8W-S(B(@.<F5L/2(B<W1Y;&5S:&5E="(B/B(-"@.ER97-P;VYS92YW<FET
M92 B/"](14%$/B(-"@.ER97-P;VYS92YW<FET92 B/$)/1%D@.=DQI;FL](B(C
M,# P,# P(B(@.;&EN:STB(B,P,# P,# B(B!B9T-O;&]R/2(B(V9F9F9F9B(B
M(&QE9G1-87)G:6X](B(P(B(@.=&]P36%R9VEN/2(B,"(B(&UA<F=I;FAE:6=H
M=#TB(C B(B!M87)G:6YW:61T:#TB(C B(CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQF;W)M(&ED/2(B9F]R;3$B(B!M971H;V0](B)G970B(B!A8W1I;VX](B(B
M("8@.<U-C<FEP=$YA;64@.("8@.(B(B/B(-"@.ER97-P;VYS92YW<FET92 B/'1A
M8FQE(&)O<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C$P,"4B(B!C
M96QL4W!A8VEN9STB(C B(B!C96QL4&%D9&EN9STB(C4B(B!W:61T:#TB(C$P
M,"4B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B(&)O<F1E<CTB(C B(CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T
M:#TB(C$P,"(B(&)G0V]L;W(](B(C8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF
M;F)S<#L\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D(&-L87-S/2(B;F5W
M+71D+79E<G1I8V%L(B(@.=VED=&@.](B(U(B(@.8F=#;VQO<CTB(B-A-V)B.#8B
M(B!H96EG:'0](B(W,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H
M:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQT9"!W:61T:#TB(C(P(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#LB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L
M:6=N/2(B=&]P(B(@.=VED=&@.](B(U,# B(B!H96EG:'0@./2 B(C@.P(B(^/$E-
M1R!H96EG:'0](B(V,"(B(&AS<&%C93TB(C(P(B(@.<W)C/2(B25,O:7-F7VQO
M9V\N9VEF(B(@.('=I9'1H/2(B,34R(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I
M=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B(&)G0V]L;W(](B(C
M8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#L\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I9'1H /2(B,3 P
M(B(@.8F=#;VQO<CTB(B,U8CDS864B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I
M9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T
M9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C,V4W-CDQ(B(@.:&5I9VAT/2(B,3 B(CX\
M24U'(&AE:6=H=#TB(C$B(B!S<F,](B))4R]S:&EM+F=I9B(B('=I9'1H/2(B
M,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,](B)N97<M
M=&0B(B!W:61T:#TB(C(P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B( B!H96EG:'0]
M(B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.
M=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S
M<STB(FYE=RUT9"(B('9!;&EG;CTB(F-E;G1E<B(B('=I9'1H/2(B-3 P(B(@.
M8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q(B(^/'-T<F]N9R!C;&%S
M<STB(G1E>'0B(B!S='EL93TB(DU!4D=)3BU,1494.B R,'!X(B(^4V5A<F-H
M.CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.]
M(B(Q,"(B/B9N8G-P.R9N8G-P.R(-"@.ER97-P;VYS92YW<FET92 B/$E.4%54
M(&ED/2(B4V5A<F-H7U!H<F%S92(B('1Y<&4](B)T97AT(B(@.<VEZ93TB(C,P
M(B(@.;F%M93TB(E-E87)C:%]0:')A<V4B(B!V86QU93TB(B(@.)B!396%R8VA?
M4&AR87-E("8@.(B(B('-T>6QE/2(B34%21TE.+51/4#H@.+31P>#L@.34%21TE.
M+4)/5%1/33H@.+3-P>"(B('9A;'5E/2(B(B(^(#QA(&ED/2(B8G1H1V\B(B!C
M;&%S<STB(F=O(B(@.:')E9CTB(FIA=F%S8W)I<'0Z9F]R;3$N<W5B;6ET*"D[
M(B(^(@.T*"7)E<W!O;G-E+G=R:71E(")';R$\+V$^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C4B(CXF;F)S<#L@./"]S
M=')O;F<^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1D/B(-"@.ER97-P;VYS92YW
M<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B)V06QI9VX](B)T;W B(B!B9T-O
M;&]R/2(B(S5B.3-A92(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER
M97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB
M#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T:#TB(C$P,"(B(&)G0V]L;W(]
M(B(C8V)E-6$V(B(@.:&5I9VAT/2(B,3 P(B(^)FYB<W [/"]T9#XB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C83=B8C@.V(B(@.:&5I9VAT/2(B,3 P(B(^
M/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB
M(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D('9!;&EG;CTB(G1O
M<"(B('=I9'1H/2(B,C B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B/B9N8G-P.SPO
M=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED
M=&@.](B(U,# B(B!H96EG:'0@./2 B(C$P,"4B(CXB#0H-"D5N9"!3=6(-"@.T*
M4W5B(%=R:71E5&%B;&5";V1Y*&E2;W=S4&5R4&%G92P@.:5-T87)T+'-38W)I
M<'1.86UE+"!S0V]N;E-T<FEN9RP@.<U-13"Q396%R8VA?4&AR87-E*0T*"41I
M;2!I3&]O<"P@.86QL9&%T82P@.;G5M8V]L<RP@.;G5M<F]W<RP@.:5-T;W L(&E2
M;W=,;V]P+"!C;VQC;W5N=&5R+"!T:&ES9FEE;&0-"@.E$:6T@.8VYN+"!R<W0L
M(&-M9"P@.<&%R86TL('!A<F%M,2P@.<&%R86TR#0H-"@.ES970@.8VYN(#T@.4V5R
M=F5R+D-R96%T94]B:F5C="@.B041/1$(N0T].3D5#5$E/3B(I#0H)8VYN+D-O
M;FYE8W1I;VY3=')I;F<@./2!S0V]N;E-T<FEN9PT*"6-N;BY/<&5N#0H-"@.ES
M970@.8VUD/5-E<G9E<BY#<F5A=&5/8FIE8W0H(D%$3T1"+D-O;6UA;F0B*0T*
M#0H)8VUD+D-O;6UA;F1497AT/2)Q=65R>2(-"@.EC;60N0V]M;6%N9%1Y<&4]
M861#;613=&]R9610<F]C#0H)8VUD+D%C=&EV94-O;FYE8W1I;VX]8VYN#0H-
M"@.E3970@.<&%R86T@./2!C;60N0W)E871E4&%R86UE=&5R*") <W1R4V5A<F-H
M(BP@.861687)#:&%R("P@.861087)A;4EN<'5T+" R,# L4V5A<F-H7U!H<F%S
M92D-"@.EC;60N4&%R86UE=&5R<RY!<'!E;F0@.<&%R86T-"@.T*"7-E="!R<W0@.
M/2!C;60N97AE8W5T90T*#0H):4QO;W @./2 Q#0H):68@.<G-T+D5/1CU&04Q3
M12!T:&5N#0H)"6%L;&1A=&$]<G-T+D=E=%)O=W,-"@.D);G5M8V]L<SUU8F]U
M;F0H86QL9&%T82PQ*0T*"0EN=6U2;W=S/75B;W5N9"AA;&QD871A+#(I*S$-
M"@.EE;'-E( T*"0EN=6U2;W=S/3 -"@.EE;F0@.:68@.#0H-"@.T*"7)S="YC;&]S
M90T*"6EF(&YU;5)O=W,\/B P('1H96X-"@.T*"0EI9B!N=6U2;W=S/BAI4F]W
M<U!E<E!A9V4@.*VE3=&%R="D@.=&AE;@.T*"0D):5-T;W ]:5)O=W-097)086=E
M("MI4W1A<G0M,0T*"0EE;'-E#0H)"0EI4W1O<#UN=6U2;W=S("TQ#0H)"65N
M9"!I9@.T*#0H)"7)E<W!O;G-E+G=R:71E(&YU;5)O=W,@.)B B(')E8V]R9',@.
M9F]U;F0@.+2!$:7-P;&%Y:6YG("(@.)B!I4W1A<G0@.*S$@.)B B('1H<F]U9V@.@.
M(B F(&E3=&]P("LQ("8@.(CQ"4CXB#0H-"@.D)268@.:5-T87)T(#X@.,"!4:&5N
M#0H)"0E297-P;VYS92Y7<FET92 B(#QA(&AR968](B F('-38W)I<'1.86UE
M("8@.(C]396%R8VA?4&AR87-E/2(F4V5A<F-H7U!H<F%S92 F("(F:5-T87)T
M/2(F*&E3=&%R="UI4F]W<U!E<E!A9V4I("8@.(CX\/"!0<F5V:6]U<SPO83XB
M#0H)"45N9"!)9@.T*#0H)"4EF(&E3=&%R=" K(&E2;W=S4&5R4&%G92 \(&YU
M;5)O=W,@.5&AE;@.T*"0D)4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)B H:5-T87)T*VE2;W=S4&5R4&%G92D@.)B B/DYE>'0^
M/CPO83XB#0H)"45N9"!)9@.T*"0ER97-P;VYS92YW<FET92 B/%1A8FQE(&)O
M<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C4E(B(@.8V5L;%-P86-I
M;F<](B(P(B(@.8V5L;%!A9&1I;F<](B(U(B(@.=VED=&@.](B(Q,# E(B(@.8F=#
M;VQO<CTB(B-F.69E960B(B!B;W)D97(](B(Q(B(^(@.T*"0ER97-P;VYS92YW
M<FET92 B/%12/B(-"@.D)<F5S<&]N<V4N5W)I=&4@.(CQ42#Y(:6=H;&EG:'1I
M;F<\+U1(/CQ42#Y296-O<F0@.3G5M8F5R/"]42#X\5$@.^0VAA<F%C=&5R:7IA
M=&EO;CPO5$@.^/%1(/D-R96%T92!$871E/"]42#X\5$@.^4VEZ93PO5$@.^/%1(
M/E)A;FL\+U1(/B(-"@.D)<F5S<&]N<V4N=W)I=&4@.(CPO5%(^(@.T*#0H)"49/
M4B!I4F]W3&]O<#T@.:5-T87)T('1O(&E3=&]P#0H)"0ER97-P;VYS92YW<FET
M92 B/%12/B(@.)B!V8F-R;&8-"@.T*"0D)1D]2(&-O;&-O=6YT97(],"!T;R!N
M=6UC;VQS#0H)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.(CQT9"!V86QI9VX]
M=&]P/B(-"@.D)(" @.(" @.("!I9B )8V]L8V]U;G1E<CTP('1H96X-"@.D)(" @.
M(" @.(" @.5V5B2&ET<U%U97)Y(#T@.(B9#:5=E8DAI='-&:6QE/2(@.)B!397)V
M97(N55),16YC;V1E*')T<FEM*&%L;&1A=&$H8V]L8V]U;G1E<BLQ+&E2;W=,
M;V]P*2DI("8B)D-I4F5S=')I8W1I;VX](B F(%-E<G9E<BY54DQ%;F-O9&4H
M4V5A<F-H7U!H<F%S92D-"@.D)(" @.(" @.(" @.4F5S<&]N<V4N5W)I=&4@.(CQ0
M/CQA(&AR968]+V]O<"]Q:&ET+FAT=S]#:4AI;&ET951Y<&4]4W5M;6%R>2(@.
M)B!796)(:71S475E<GD@.)B B/CQ)34<@.<W)C/2(@.)B(O:&EL:6=H="YG:68B
M("8@.(B!A;&EG;CUL969T(&%L=#TB("8@.(DAI9VAL:6=H="!M8 71C:&EN9R!T
M97)M<R!I;B!D;V-U;65N="!U<VEN9R!3=6UM87)Y(&UO9&4N(B F("(^/% ^
M4W5M;6%R>3PO83X\4#XB#0H@.( D)"0D)"5)E<W!O;G-E+E=R:71E("(\0E(^
M/% ^/% ^/&$@.:')E9CTO;V]P+W%H:70N:'1W/T-I2&EL:71E5'EP93U&=6QL
M(B F(%=E8DAI='-1=65R>2 F("(^/$E-1R!S<F,](B8@.("(O:&EL:6=H="YG
M:68B)B(@.86QI9VX];&5F="!A;'0](B8B2&EG:&QI9VAT(&UA=&-H:6YG('1E
M<FUS(&EN(&1O8W5M96YT+B(F(CX\4#Y&=6QL/"]A/CQ0/B(-"@.D)(" @.(" @.
M("!E;'-E:68@."6-O;&-O=6YT97(],2!T:&5N#0H)"2 @.(" @.(" @."7)E<W!O
M;G-E+G=R:71E("(\82!H<F5F/2(B+V5D:70N87-P/U!+/2(F86QL9&%T82AC
M;VQC;W5N=&5R+3$L:5)O=TQO;W I)B(B(CXB("8@.(&E2;W=,;V]P*S$@.)B B
M/"]A/B(-"@.D)(" @.(" @.("!E;'-E#0H)"0D)(" @.(" @.("!T:&ES9FEE;&0]
M86QL9&%T82AC;VQC;W5N=&5R+&E2;W=,;V]P*0T*"0D)"2 @.(" @.(" @.:68@.
M:7-N=6QL*'1H:7-F:65L9"D@.=&AE;@.T*"0D)"0D)"0D@.('1H:7-F:65L9#US
M:&]W;G5L; T*"0D)"0D)"0EE;F0@.:68-"@.D)"0D)"0D):68@.=')I;2AT:&ES
M9FEE;&0I/2(B('1H96X-"@.D)"0D)(" )"0ET:&ES9FEE;&0]<VAO=V)L86YK
M#0H)"0D)"0D)96YD(&EF#0H-"@.D)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.
M=&AI<V9I96QD#0H)"0D@.(" @.96YD(&EF#0H)"2 @.(" @.(" @.<F5S<&]N<V4N
M=W)I=&4@.(CPO=&0^(@.T*"0D@.(" @.($Y%6%0-"@.D)(" @.("!R97-P;VYS92YW
M<FET92 B/"]T<CXB#0H)"4Y%6%0-"@.T*#0H)"7)E<W!O;G-E+G=R:71E("(\
M+W1A8FQE/B(-"@.D);VX@.97)R;W(@.9V]T;R P#0H)"4EF(&E3=&%R=" ^(# @.
M5&AE;@.T*(" @.(" @.(" )4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)BAI4W1A<G0M:5)O=W-097)086=E*2 F("(^/#P@.4')E
M=FEO=7,\+V$^(@.T*"0E%;F0@.268-"@.T*"0E)9B!I4W1A<G0@.*R!I4F]W<U!E
M<E!A9V4@./"!N=6U2;W=S(%1H96X-"@.D)"5)E<W!O;G-E+E=R:71E("(@./&$@.
M:')E9CTB("8@.<U-C<FEP=$YA;64@.)B B/U-E87)C:%]0:')A<V4](B9396%R
M8VA?4&AR87-E("8@.(B9I4W1A<G0](B8@.*&E3=&%R="MI4F]W<U!E<E!A9V4I
M("8@.(CY.97AT/CX\+V$^(@.T*"0E%;F0@.268-"@.EE;'-E#0H)"7)E<W!O;G-E
M+G=R:71E(").;R!M871C:&EN9R!R96-O<F1S(&9O=6YD+B(-"@.EE;F0@.:68-
M"@.T*96YD('-U8@.T*#0I3=6(@.5W)I=&5486)L949O;W1E<@.T*#0H)<F5S<&]N
M<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,]
M(B)N97<M=&0M=F5R=&EC86PB(B!B9T-O;&]R/2(B(V-B935A-B(B(&AE:6=H
M=#TB(C$P,"(B/B9N8G-P.SPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1R
M/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D(B(@.=VED=&@.](B(Q,# B(B!B9T-O;&]R/2(B
M(V$W8F(X-B(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C
M/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B(@.8F=#;VQO<CTB(B,S93<V
M.3$B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(FEM
M86=E<R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E
M+G=R:71E("(\=&0@.8VQA<W,](B)N97<M=&0B(B!W:61T:#TB(C(P(B(@.8F=#
M;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B
M,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('9!;&EG; CTB
M(G1O<"(B('=I9'1H/2(B-3 P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG
M:'0](B(Q,"(B/B(-"@.ER97-P;VYS92YW<FET92 B/' @.8VQA<W,](B)P+6QI
M9VAT(B(^*&,I(&-O<'ER:6=H=#QA(&AR968](B)H='1P.B\O=W=W+G-M:6QL
M82YR=2(B/B9N8G-P.VAT=' Z+R]W=W<N<VUI;&QA+G)U/"]A/CPO<#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.
M8VQA<W,](B)N97<M=&0B(G9!;&EG;CTB(G1O<"(B(&)G0V]L;W(](B(C-6(Y
M,V%E(B(@.:&5I9VAT/2(B,3 B(CX\24U'(&AE:6=H=#TB(C$B(B!S<F,](B))
M4R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R
M:71E("(\+W1R/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D('=I9'1H/2(B,3 P(B(@.8F=#;VQO<CTB(B-C8F4U838B
M(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI
M;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.8F=#;VQO<CTB(B-A-V)B
M.#8B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D('=I9'1H/2(B,C B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0]
M(B(Q(B(@.<W)C/2(B25-S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*
M"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED=&@.](B(U
M,# B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.=D%L:6=N/2(B=&]P
M(B(@.8F=#;VQO<CTB(B-C8F4U838B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG
M:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D
M/B(-"@.ER97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CPO=&%B;&4^(@.T*"7)E<W!O;G-E+G=R:71E("(\+V9O<FT^(@.T*"7)E<W!O
M;G-E+G=R:71E("(\2#$^(@.T*"7)E<W!O;G-E+G=R:71E("(\+T)/1%D^(@.T*
J"7)E<W!O;G-E+G=R:71E("(\+TA434P^(@.T*16YD(%-U8@.T*)3X-"@.T*
`
end
|||Thomas,
Take a look at a sample script I wrote for paging resultsets:
http://www.sqlservercentral.com/scri...tions/1233.asp
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CbZeXGrEHA.332@.TK2MSFTNGP14.phx.gbl...
> You are using the wrong approach for this.
> Here is some sample code that uses asp and getrows.
> Approaches like the one you are using do not offer the best performance.
> Check out
> http://www.adopenstatic.com/experime...dsetpaging.asp
> for more information.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
> message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> often
> the
> in-memory
> and
> to
> and
>
>

"Login Failed" for SQL Express

Whenever I try to connect to my SQL Express Database with C# code,(.net 2.0), it says "Login failed for user 'TUser'. The user is not associated with a trusted SQL Server connection."

But I can connect with SQL Express Manager.

I've heard somewhere this is a SQL authentication problem, I didn't enable it during installation.

So is there a way to fix this? How can I enable SQL authentication without uninstalling it?

Is there anyone to help me?

"Login failed for user" on external webhosting

I get the following error message when trying to access SQL server database hosted by Godaddy.com. I've enabled ASP.net 2.0 and setup SQL 2005 schema, followed their directions for the connectionstring and am using the SQL account and password I setup on Godaddy's website to access the SQL database. When I attempt to use the built in create user or login form that I created from visual studio express that works while using sql 2005 express on my localmachine, it gives the following error message:

Login failed for user 'ysatrifecta'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'ysatrifecta'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Login failed for user 'ysatrifecta'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734931
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Can anyone help?

I had misspelled the hostname in the connectionstring...doh!

"Login failed for user" error

hi ppl,
I'm having trouble connecting to my database using ASP.NET
I have been getting the error below whenever i try open the connection using connection.open using vb.net

I have this error in when using plain text editor as and even using
the visual studio.nets data tools (sqlconnection,sqldataadapter tool.)
The strange thing is that i can easily preview the data in any table in northwind db using the connections i've created in the solution explorer using visual studio.net.

This is the connection string created by visual studio.net (i also tried to use this connectionstring manually in code)
"initial catalog=Northwind;integrated security=SSPI;persist security info=False;workstation id=COMPI;packet size=4096"

Here is the error (same error for plain text and vs.net):

Login failed for user 'COMPI\ASPNET'
...
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'COMPI\ASPNET'.
...
[SqlException: Login failed for user 'COMPI\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
BegCh12.WebForm1.Page_Load(Object sender, EventArgs e) in C:\BegASPNet\BegCh12\WebForm1.aspx.vb:153
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()...

can someone help pls
Im using sql server 7 sp3, windows xp pro.I may have miss read this so let me restate your problem.

You have an application thaat can view and access data in the NorthWind DB but not the DB your app is written for. If that is true then it sounds like your userid "ASPNET" needs to be granted access to the db in question as well as execute or Select/Delete/Update permissions of all needed objects.|||How do i do what ur saying, is it from SQL server or using Windows XP.
I have just installed sql server and havent done any changes to permission or account (not sure how)?|||Use Enterprise Manager

Open your server, expand Security and then click on Users. right click on your UserID and select properties. Under the Database Access tab click on the Permit check box next to your database. Under the Permit in Database Role, you will want to select public, and probably db_datareader/datawrite OR you can open things up a bit and select db_owner, but I wouldn't use it for very long.

Books Online covers this very well.

"Login failed for user" error

Hi

I have installed sql server on my standalone PC.

I used "window and sql mix authentication" for the sql server in the enterprise manager set up.
when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
So Even though I use mix authentication, I am still using window login name.

Then I create the DSN file.

But when I run the ASP program, I have this error "login fail for user-("user name that I used for dsn file configuration").

I try to configure the dsn file set up with both authentications, windows and sql login names.

But I got the error again.

I am really lost here.
Please help me. thanks...What's your connection string?|||when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".

You cannot use a sql server login for the service accounts, these need to be windows accounts (domain or local user, or system account). The mixed authentication only applies to logins to the server.

"Login failed for user MyPC/ASPNET"

Hi

I created my DB in SQL 2005 Express in VS IDE. I have my app in IIS. When I try to use the DB (e.g. try to login a user in the app) it gives me the following error:

Cannot open database "cvRepository" requested by the login. The login failed.
Login failed for user 'MyPC\ASPNET'.

My guess was that I had to give permissions to ASPNET user so I did in SQL Server Management. Still not working! Checked my Connection string and I think it's fine...

connectionString="Data Source=.\SQLEXPRESS;Database=cvRepository;Integrated Security=True;User Instance=True"

Still not working!

Can anyone explain me what's wrong and to solve this?!

Try replacing Database=cvRepository withAttachDBFileName=|DataDirectory|cvRepository.mdfin your connectionString.

Hope this helps.

Mike

|||Do you have a database namedcvRepositoryon your SQL server? Or you're using a database file attached at runtime? Anyways you have to ensure the ASPNET account has login permission to the SQL server, as well as access permissions on the required database.|||

Hi

Tried using the AttachDBFileName but nothing:

<

addname="cvRepositoryConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDBFileName=|DataDirectory|cvrepository.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />I don't understand much of this but I'll shoot out something... When running the app in debug mode (http://localhost:2431/cvRepository/Default.aspx) it creates an instance of the SQL Server but when not doing so (http://localhost/cvRepository/Default.aspx) it doesn't, that's why I can't access the DB. Is this correct? If so, how to solve it?

Any explanation/article would be helpful!

Thx

|||Try to add the ASPNET login to a member ofsysadminordbcreatorserver role.|||

Hi

Instead of using User Instances I defined a particular user to access the DB. Used this:

connectionString

="Server=.\SQLEXPRESS;Database=cvrepository;User ID=user1;Password=user1pass"

Thx for your help

Cheers

"INSERT INTO" - Question

Hello,

if I have a row full of Data, lets call it Row "A". Now i have the "Insert Into" Command in my Application. So, when the User executes the Command again, will it update Row "A" or will it produce just a double?

This is important for my further Work with SQL.

I am using SQL Server 2006 Express or something like this. The Table in which Row "A" is has no primary key!

Greetz,

Eroli

Unless you have constraints/triggers or your INSERT violates the defaults/constraints, it will add a NEW ROW to the table irrespective of the existing data.

|||

Hi,

but when i put it all into a for-command like this one

for(int i = 0; i != 5;i++)

{

//All the Commands

}

I get only one row.

So, whats right now?

Greetz,

Eroli

|||Depends on what the commands are. Post all the relevant code and that might help.|||

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "INSERT INTO UsersTable([User], [FirstStart], [Commercial], [Name], [MailAddresse], [Addresse], [Addresse2], [Location], [PostCode], [Country], [PhoneNumber], [PhoneNumber2], [Sex], [ShowLastBoughts], [StartPage], [Notifications], [RemainingTime], [HomepageUrl], [Employees], [Owner], [Evaluations]) VALUES (@.User,@.FirstStart,@.Commercial, @.Name,@.MailAddresse,@.Addresse,@.Addresse2,@.Location,@.PostCode,@.Country,@.PhoneNumber,@.PhoneNumber2,@.Sex,@.ShowLastBoughts,@.StartPage,@.Notifications,@.RemainingTime,@.HomepageUrl,@.Employees,@.Owner,@.Evaluations)";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", -1);
SqlCommand.Parameters.AddWithValue("@.Commercial", -1);
SqlCommand.Parameters.AddWithValue("@.Name", "");
SqlCommand.Parameters.AddWithValue("@.MailAddresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse2", "");
SqlCommand.Parameters.AddWithValue("@.Location", "");
SqlCommand.Parameters.AddWithValue("@.PostCode", -1);
SqlCommand.Parameters.AddWithValue("@.Country", "");
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", -1);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", -1);
SqlCommand.Parameters.AddWithValue("@.Sex", "");
SqlCommand.Parameters.AddWithValue("@.ShowLastBoughts", "");
SqlCommand.Parameters.AddWithValue("@.StartPage", -1);
SqlCommand.Parameters.AddWithValue("@.Notifications", -1);
SqlCommand.Parameters.AddWithValue("@.RemainingTime", -1);
SqlCommand.Parameters.AddWithValue("@.HomepageUrl", "");
SqlCommand.Parameters.AddWithValue("@.Employees", -1);
SqlCommand.Parameters.AddWithValue("@.Owner", "");
SqlCommand.Parameters.AddWithValue("@.Evaluations", "");
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

I've got only one Row.

|||

(1) Do you have any constraints/triggers on the table?

(2) Are you changing the values of the parameters for each loop or are youe xpecting to insert 5 rows with same values?

|||

The User Column will be everytime the same, but maybe some other colums will change.

Is the UPDATE-Command better? When yes, so how can i use this?

Im confused, because i have now more rows when i tried my application again. Seems your are right.

|||You need to explain more clearly what you are trying to do and what you intend to do. INSERT and UPDATE are 2 different commands that accomplish 2 different things. so there's no question of one "better" over another. You need to use either one depending on what how application needs to behave. Simply, INSERT inserts data into the table (meaning rows will increase), UPDATE updates te existing data (and how many rows affected depends on the WHERE condition in the query).|||

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

|||

Eroli:

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Yes.

Eroli:

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

Yes. You need to do put your logic accordingly.

|||Ok, thanks for your advice!|||

Ok, i changed my Commands.

This is my UPDATE Command:

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();</p><p>

It is in an ASP.NET Webapplication in C#! I get no failure, but nothing gets updated...

The obversely Row is filled with Dummy-Values before starting this Command.(MS SQL Server 2005 Express or so)

Greetz,

Eroli

|||

I hope this looks better...

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

|||

Ok, i solved it: I have just forgotten to put the [ ] round my Columns.

But there is another Problem: THe Condition does not work, because the User Column is no Identify-Column. How to solve this?

|||I did not understand your question. Can you rephrase?

"Failed to generate a user instance of SQL Server" using sql server express and vwde. why?

I've set up a website on another computer using sql server express and visual web developer express, and it works as expected. However halfway through the development i've had to move to another computer. I installed visual web developer express + sql server express, and copied the project files across, but now when I attempt to run it I get the error:

Failed to generate a user instance of SQL Server due to afailure in starting the process for the user instance. The connectionwill be closed.

I've tried reinstalling the software and it doesn't help. Any idea why this is happening, and what I can do to fix it?

thanks

How to fix it is covered in the link below change the configuration. Hope this helps.

http://blog.krisvandermast.com/FailedToGenerateAUserInstanceOfSQLServerDueToAFailureInStartingTheProcessForTheUserInstanceTheConnectionWillBeClosed.aspx

|||

Hi,

I changed the built in account to 'local system' in the SQL server configuration manager, and deleted my SQLEXPRESS folder, then restarted the computer, but the same thing is happening. Is there anything else I need to do to fix this?

thanks for your help

|||

I am sorry you error sent me on the wrong track because moving a database created in one box and moving it to another is not a simple copying files over. That is the reason the solution I gave you did not fix your problem so delete the folder. When you are moving your database to a new computer you need to do it manually through code like the second link below. You need to check your access permissions to the new computer and follow the guide created by Microsoft in the second link if the forum posting in the first link below did not fix your problem. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

"Failed to generate a user instance of SQL Server" - Redux

I have SQL Server 2005 SP1 Dev Edition installed on my laptop along with Visual Web Developer 2005. I installed SQL Server Express 2005 SP1. I then created a new web site using the Club Web Site template.

I did the recommended initial build to get the Membership database installed and got an error page (http://localhost:1667/WebSitesVB2/):

SQLExpress database file auto-creation error:
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.

There were 4 recomme4ndations from the error page. I have met three of them: (1) the AppDaa folder that was created when I used the Club Web Site template; (2) my machine's ASPNET user set up with full control permissions on the APP Data Folder; (3) SQL Server Express successfully installed on my machine.

The 4th recommendation from the error page is "The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. ". I have no idea of how to do this or how/where to find the elusive the "readme" file.

I am at a loss as to what to do next. I have spent the day going over forums on this error and none seem to provide consistent or relevant answers.

Try to remove User Instance=true from your connection string, if you don't need to generate a user instance at runtime. For more information about the user instance attribute, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx|||

Changed userinstance = TRUE to userinstance=FALSE. Same result - failure to generate userinstance.

Is it possible that there is some setting in VWD that provides a connection string for the ASPNET db that is to be created? The only connection string in the WebConfig from the original template is the one for the ClubSite DB. There is none for the ASPNET db (because the template does not provide the ASPNET DB.

After 2 tries, I still get this error and when I look at the database explorer, there is only one DB file: Club.MDF.

Anyone else have any answers?

|||

I probably should have included this stack trace:

Stack Trace:

[SqlException (0x80131904): 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.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1084 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +355 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +91 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +115 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +110[HttpException (0x80004005): Unable to connect to SQL Server database.] System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +181 System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +167 System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +46 System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +681

---

|||Sorry I forgot to mention: there is a connection string named LocalSqlServer, which is used by .NET 2.0 built-in providers (Profile, Membership, RoleManager, etc.). You can override it and try again, for details you can refer to:
Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005