Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Tuesday, March 27, 2012
Attach Database Basic Question
When one of our clients tries to attach a database (about 6GB in size)
she gets a message saying file size limit exceeded. She is using Sql
Server 2000. Is there a limitation on size for Sql Server 2000? I know
for MSDE the size can not be more than 2GB. Can you shed any light on
the issue please?Strange, and is there enough space on the file system for the database?
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1147856960.608383.244690@.i39g2000cwa.googlegroups.com...
>
> When one of our clients tries to attach a database (about 6GB in size)
> she gets a message saying file size limit exceeded. She is using Sql
> Server 2000. Is there a limitation on size for Sql Server 2000? I know
> for MSDE the size can not be more than 2GB. Can you shed any light on
> the issue please?
>|||Sounds like your client is using MSDE or SQL Server which has limit of data
storage for a database
(2 vs 4 GB).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1147856960.608383.244690@.i39g2000cwa.googlegroups.com...
>
> When one of our clients tries to attach a database (about 6GB in size)
> she gets a message saying file size limit exceeded. She is using Sql
> Server 2000. Is there a limitation on size for Sql Server 2000? I know
> for MSDE the size can not be more than 2GB. Can you shed any light on
> the issue please?
>
Sunday, March 25, 2012
Attach Access Database to SQLServer
Attach Access Database to SQLServer?
It seems it is necessary if I want to put it on the internet through IIS.
I tried add data source through tools and tried most combinations, but nothing led in that direction.
I also did a search.
If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?
dennist685
> It seems it is necessary if I want to put it on the internet through IIS.
No. My guess is that you might security issues. The account of which runs your statement (IIS account or ASP account, I guess) to have the rights to read your mdb file. Should be mentioned in numerous threads.
> If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?
This would be my soulution. I would use SQL Server (maybe Express) to serve data.
You can access Access tables from within SQL Server queries. I would keep away from doing this for normal processing. For import of data, it is OK, I guess.
-- Sample to SELECT against object in access db:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\dir\mymdb.mdb';'admin';'',objectname)
GO
The sp_configure statments are neccessary because distributed queries are turned off by default for security reasons
Hope this helps
sqlThursday, March 22, 2012
At what point is schema information loaded?
I am hoping that someone with a good knowledge of the internals of
SQLServer will be able to help with the following - admittedly bizarre
- question (assume SQLServer 2000 throughout):
We have two SQLServer databases, dbmain and dbslave. An application
calls stored procedures in dbmain, some of which in turn access tables
in dbslave, ie we have something like this:
create procedure some_sp_in_dbmain_called_by_app
as
select * from dbslave.dbo.someTable
Significantly, dbslave is only ever accessed in this way, ie via
dbmain.
The question is:
At what point does the SQLServer engine load information about dbslave
from disk? Presumably in order to fulfil the select query in the
example it needs to have schema information for dbslave at that point,
but is this information loaded on demand at the point of the call, or
is information on all databases loaded when the server is started?
I should explain that this question is motivated by a legal case I am
working on, where it is important that we establish the point at which
any part of dbslave is copied into RAM from disk. For this reason, any
links to pertinent documentation or papers would be particularly
welcome.
TIA for any help.Some but generally not all the meta-data is loaded when the database is
attached when the sql server service starts - generally when the machine
boots. Given read-ahead, recovery, and shared extents it's pretty much
impossible to predict when a given piece of meta-data is loaded into memory.
If there are a lot of transactions that need to be recovered, a significant
portion of the schema information might be loaded at startup.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177016287.857608.294980@.q75g2000hsh.googlegroups.com...
> I am hoping that someone with a good knowledge of the internals of
> SQLServer will be able to help with the following - admittedly bizarre
> - question (assume SQLServer 2000 throughout):
> We have two SQLServer databases, dbmain and dbslave. An application
> calls stored procedures in dbmain, some of which in turn access tables
> in dbslave, ie we have something like this:
> create procedure some_sp_in_dbmain_called_by_app
> as
> select * from dbslave.dbo.someTable
> Significantly, dbslave is only ever accessed in this way, ie via
> dbmain.
> The question is:
> At what point does the SQLServer engine load information about dbslave
> from disk? Presumably in order to fulfil the select query in the
> example it needs to have schema information for dbslave at that point,
> but is this information loaded on demand at the point of the call, or
> is information on all databases loaded when the server is started?
> I should explain that this question is motivated by a legal case I am
> working on, where it is important that we establish the point at which
> any part of dbslave is copied into RAM from disk. For this reason, any
> links to pertinent documentation or papers would be particularly
> welcome.
> TIA for any help.
>|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||Profuse apologies for the triplicate post - Google groups appears to
be a bit skittish today ...|||First, you can't ignore recovery - a database goes through recovery every
time you start it. Second, there's no way to determine when a piece of
schema get loaded into memory. If a page from sysobjects is read to access
a table, it might have information on dozens of other tables on the page.
SQL also does read-ahead so accessing one page might cause a dozen to be
read into memory. I can't see what possible use this would be to anybody
anyway. Whether data is one disk or in memory is a pretty arbitrary
distinction that may be different every time a database starts. You could
detach the database when you shut down and only attach it when you need it
but that seems rather silly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177076014.570168.111420@.p77g2000hsh.googlegroups.com...
> On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
> wrote:
>> Some but generally not all the meta-data is loaded when the database is
>> attached when the sql server service starts - generally when the machine
>> boots. Given read-ahead, recovery, and shared extents it's pretty much
>> impossible to predict when a given piece of meta-data is loaded into
>> memory.
>> If there are a lot of transactions that need to be recovered, a
>> significant
>> portion of the schema information might be loaded at startup.
> Roger,
> Thanks very much for your reply. I understand that there isn't
> necessarily a simple answer to this, but if I may, let me see if I can
> paraphrase, assuming a simple startup scenario with no recovery:
> = When you start the sql server service, it attaches your databases
> (including master and (from my example) dbmain and dbslave;
> = For each database attached (still at startup time here) sql server
> reads *some* of the data from *some* of the sys- tables and uses this
> to build an internal in-memory representation of the database's schema
> (btw I'm guessing that master is in some senses a special case, but I
> am less interested in this).
> If I have this correct, is it possible to qualify the *some*s any
> further? For example, will it construct a representation of all tables
> using data from sysobjects et al, but delay examining stored procedure
> code from syscomments until they are actually accessed, something of
> that sort? Or is the algorithm more subtle than this?
> Many thanks again.
>|||On 20 Apr, 15:50, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> First, you can't ignore recovery - a database goes through recovery every
> time you start it.
OK, I hadn't appreciated that.
> Second, there's no way to determine when a piece of
> schema get loaded into memory. If a page from sysobjects is read to access
> a table, it might have information on dozens of other tables on the page.
> SQL also does read-ahead so accessing one page might cause a dozen to be
> read into memory.
Of course. I should have realised this, and this is actually the
clincher.
> I can't see what possible use this would be to anybody
> anyway. Whether data is one disk or in memory is a pretty arbitrary
> distinction that may be different every time a database starts. You could
> detach the database when you shut down and only attach it when you need it
> but that seems rather silly.
I agree that the whole line of inquiry seems bizarre, but we are
labouring under a very precise definition of "copying" that does not
really apply very naturally to software, especially "meta-software"
such as a database schema, and the intention was to try and scope just
how much of the schema is "copied" from disk to memory. But from your
explanations above I can now see precisely how my original question
does not have a sensibly deterministic answer, so that's actually a
big help in and of itself. Many thanks for your help.sql
SQLServer will be able to help with the following - admittedly bizarre
- question (assume SQLServer 2000 throughout):
We have two SQLServer databases, dbmain and dbslave. An application
calls stored procedures in dbmain, some of which in turn access tables
in dbslave, ie we have something like this:
create procedure some_sp_in_dbmain_called_by_app
as
select * from dbslave.dbo.someTable
Significantly, dbslave is only ever accessed in this way, ie via
dbmain.
The question is:
At what point does the SQLServer engine load information about dbslave
from disk? Presumably in order to fulfil the select query in the
example it needs to have schema information for dbslave at that point,
but is this information loaded on demand at the point of the call, or
is information on all databases loaded when the server is started?
I should explain that this question is motivated by a legal case I am
working on, where it is important that we establish the point at which
any part of dbslave is copied into RAM from disk. For this reason, any
links to pertinent documentation or papers would be particularly
welcome.
TIA for any help.Some but generally not all the meta-data is loaded when the database is
attached when the sql server service starts - generally when the machine
boots. Given read-ahead, recovery, and shared extents it's pretty much
impossible to predict when a given piece of meta-data is loaded into memory.
If there are a lot of transactions that need to be recovered, a significant
portion of the schema information might be loaded at startup.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177016287.857608.294980@.q75g2000hsh.googlegroups.com...
> I am hoping that someone with a good knowledge of the internals of
> SQLServer will be able to help with the following - admittedly bizarre
> - question (assume SQLServer 2000 throughout):
> We have two SQLServer databases, dbmain and dbslave. An application
> calls stored procedures in dbmain, some of which in turn access tables
> in dbslave, ie we have something like this:
> create procedure some_sp_in_dbmain_called_by_app
> as
> select * from dbslave.dbo.someTable
> Significantly, dbslave is only ever accessed in this way, ie via
> dbmain.
> The question is:
> At what point does the SQLServer engine load information about dbslave
> from disk? Presumably in order to fulfil the select query in the
> example it needs to have schema information for dbslave at that point,
> but is this information loaded on demand at the point of the call, or
> is information on all databases loaded when the server is started?
> I should explain that this question is motivated by a legal case I am
> working on, where it is important that we establish the point at which
> any part of dbslave is copied into RAM from disk. For this reason, any
> links to pertinent documentation or papers would be particularly
> welcome.
> TIA for any help.
>|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||Profuse apologies for the triplicate post - Google groups appears to
be a bit skittish today ...|||First, you can't ignore recovery - a database goes through recovery every
time you start it. Second, there's no way to determine when a piece of
schema get loaded into memory. If a page from sysobjects is read to access
a table, it might have information on dozens of other tables on the page.
SQL also does read-ahead so accessing one page might cause a dozen to be
read into memory. I can't see what possible use this would be to anybody
anyway. Whether data is one disk or in memory is a pretty arbitrary
distinction that may be different every time a database starts. You could
detach the database when you shut down and only attach it when you need it
but that seems rather silly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177076014.570168.111420@.p77g2000hsh.googlegroups.com...
> On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
> wrote:
>> Some but generally not all the meta-data is loaded when the database is
>> attached when the sql server service starts - generally when the machine
>> boots. Given read-ahead, recovery, and shared extents it's pretty much
>> impossible to predict when a given piece of meta-data is loaded into
>> memory.
>> If there are a lot of transactions that need to be recovered, a
>> significant
>> portion of the schema information might be loaded at startup.
> Roger,
> Thanks very much for your reply. I understand that there isn't
> necessarily a simple answer to this, but if I may, let me see if I can
> paraphrase, assuming a simple startup scenario with no recovery:
> = When you start the sql server service, it attaches your databases
> (including master and (from my example) dbmain and dbslave;
> = For each database attached (still at startup time here) sql server
> reads *some* of the data from *some* of the sys- tables and uses this
> to build an internal in-memory representation of the database's schema
> (btw I'm guessing that master is in some senses a special case, but I
> am less interested in this).
> If I have this correct, is it possible to qualify the *some*s any
> further? For example, will it construct a representation of all tables
> using data from sysobjects et al, but delay examining stored procedure
> code from syscomments until they are actually accessed, something of
> that sort? Or is the algorithm more subtle than this?
> Many thanks again.
>|||On 20 Apr, 15:50, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> First, you can't ignore recovery - a database goes through recovery every
> time you start it.
OK, I hadn't appreciated that.
> Second, there's no way to determine when a piece of
> schema get loaded into memory. If a page from sysobjects is read to access
> a table, it might have information on dozens of other tables on the page.
> SQL also does read-ahead so accessing one page might cause a dozen to be
> read into memory.
Of course. I should have realised this, and this is actually the
clincher.
> I can't see what possible use this would be to anybody
> anyway. Whether data is one disk or in memory is a pretty arbitrary
> distinction that may be different every time a database starts. You could
> detach the database when you shut down and only attach it when you need it
> but that seems rather silly.
I agree that the whole line of inquiry seems bizarre, but we are
labouring under a very precise definition of "copying" that does not
really apply very naturally to software, especially "meta-software"
such as a database schema, and the intention was to try and scope just
how much of the schema is "copied" from disk to memory. But from your
explanations above I can now see precisely how my original question
does not have a sensibly deterministic answer, so that's actually a
big help in and of itself. Many thanks for your help.sql
Asynchronous / non-blocking trigger
Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
Jon
Triggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>
|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>
|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
Jon
Triggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>
|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>
|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>
Asynchronous / non-blocking trigger
Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/s...br />
ers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/s...br />
ers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>
AsyncExecute problem
Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:
mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@.0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@.1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();
The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.
BUT: NO DATA IS FILLED IN THE TABLE.
I've tested that begin-commit works by adding a call to another sp, and
it works fine.
The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.
The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.
The sp works fine when executed from Query Analyzer.
Has anyone got any ideas? Am I missing something?
Thanks,
Juhu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!juhu (juhu@.bruhu.com) writes:
> The thing is that the state is adStateExecuting for about 16 seconds
> (30days interval), and afterwards drops to 0 (adStateClosed), while the
> sp is still executing (which usually takes about a minute). The process
> waits at the CommitTrans() until the sp is done. There aren't any errors
> reported or exceptions thrown.
I don't have any experience of asynchronous queries. But it could be
that the command timeout strikes. This timeout is by default 30 seconds.
Set it to 0 to get rid of it:
cmd->CommandTimeout = 0;
My first thought was that you have a pseudo-recordset in form of a
rowcount from an INSERT into a temp table. But since it works with
smaller amount of data it may not be that. Nevertheless, add a SET
NOCOUNT ON in the stored procedure if you don't have one.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Thursday, March 8, 2012
Assign session variable value to update parameter
Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.
code snippet:
<UpdateParameters><asp:ParameterName="hrs_credited"/><asp:ParameterName="updater_id"DefaultValue="<%$ Session("User_ID")%>"Type="Int32"/>
<asp:ParameterName="activity_id"/>
<asp:ParameterName="attendee_id"/>
</UpdateParameters>The error message that I receive is:
Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:\Development\CME\dataentry\attendance.aspx 29
Does anyone have an idea how to assign the session var value to the parameter?
Thanks!
There is a special parameter called a SessionParameter that does exactly that. Refer to this page for more information:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter.aspx
Wednesday, March 7, 2012
Assertion errors? Error: 3624, Severity: 20, State: 1
I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:
> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:
> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Assertion errors? Error: 3624, Severity: 20, State: 1
I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on
a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on
a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Assertion errors? Error: 3624, Severity: 20, State: 1
I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Saturday, February 25, 2012
AspX Page Connection Error using sqlserver
Dear i am using visual studio.net........ when i connect database (in sqlserver) using sqldataAdapter with datagrid in visual basic.net every thing work properly......... but when i use the same in asp.net then i get an error message on the resultant explorer page give below.
Server Error in '/studentData' Application.
------------------------
Login failed for user 'RAMIZSARDAR\ASPNET'.
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 'RAMIZSARDAR\ASPNET'.
Source Error:
Line 85: 'Put user code to initialize the page here
Line 86: Dim ds As New DataSet()
Line 87: SqlDataAdapter1.Fill(ds)
Line 88: DataGrid1.DataSource = ds.Tables(0)
Line 89: DataGrid1.DataBind()
Source File: c:\inetpub\wwwroot\studentData\WebForm1.aspx.vb Line: 87
Stack Trace:
[SqlException: Login failed for user 'RAMIZSARDAR\ASPNET'.]
System.Data.SqlClient.SqlConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
studentData.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\studentData\WebForm1.aspx.vb:87
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
Plz solve my problem and Reply me on ramiz_ch@.hotmail.com
Plz solve my problem and Reply me on ramiz_ch@.hotmail.com
Plz solve my problem and Reply me on ramiz_ch@.hotmail.com
Ramizwell, yes. if you use the same code in VB.NET/WIndows forms it'll run under the security context of the currently logged-in user. i.e. YOU.
under ASP.NET it runs by default as the ASP.NET guest account, MACHINENAME\ASPNET
you need to add ASPNET to the SQL Server permissions list, OR run ASP.NET as a different user, OR specify a SQL Server auth account in your connection string.
this question has been covered over and over in these forums. try the FAQ, if it's not in there I'll be shocked and amazed.
Sunday, February 19, 2012
ASP-Application
Hi,
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel RakojevicYou could run your application on the IIS server under the context of an acc
ount (domain or local to the SQL server), grant this account access to the d
atabase, and then set the DSN on the IIS box to use trusted_connection=YES.
if you need to secure the data in transit, you could also enable SSL encrypt
ion on the SQL Server:
http://support.microsoft.com/defaul...kb;en-us;316898
--Roberto
-- Daniel Rakojevic wrote: --
Hi,
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel Rakojevic
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel RakojevicYou could run your application on the IIS server under the context of an acc
ount (domain or local to the SQL server), grant this account access to the d
atabase, and then set the DSN on the IIS box to use trusted_connection=YES.
if you need to secure the data in transit, you could also enable SSL encrypt
ion on the SQL Server:
http://support.microsoft.com/defaul...kb;en-us;316898
--Roberto
-- Daniel Rakojevic wrote: --
Hi,
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel Rakojevic
Thursday, February 16, 2012
ASP.net/SQL SERVER database Question
I am new to this but here goes.
I have a sqlserver with asp.net.I have a stored procedure in the database
named Try_Login
I have a sqlserver with asp.net.I have a stored procedure in the database
named Try_Login
CREATE procedure dbo.Try_Login
(
@.LoginName nvarchar(15),
@.Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password
What I need to know is how to get my code to correspond with it.I.E. I have a login in form...
txtUsername and txtPassword need to be passed to this Stored procedure.
I assume you use .value to obtain thier values.Here is my code so far.....
Public Sub LoginDB()
Dim conLogin As SqlConnection
Dim cmdLogin As SqlCommand
Dim dtrLogin As SqlDataReader
conLogin = New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
cmdLogin = New SqlCommand("Try_Login ", txtUsername.Value, txtPassword.Value, conLogin)
cmdLogin.CommandType = CommandType.StoredProcedure
dtrLogin = cmdLogin.ExecuteReader()
While dtrLogin.Read()
End While
End Sub
I have no clue why it does not work........You need to add parameters:
cmdLogin = New SqlCommand("Try_Login ", conLogin)cmdLogin.CommandType = CommandType.StoredProcedure
cmd.Login.Parameters.Add("@.LoginName",txtUsername.Text)
cmd.Login.Parameters.Add("@.Password",txtPassword.Text)dtrLogin = cmdLogin.ExecuteReader()
While dtrLogin.Read()End While
ASP.Net, Microsoft SQL Server 2005 and XML
Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
oAs the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
oBut After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
oThe problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.
Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.
> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> oAs the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> oBut After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> oThe problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Also instead of trying to construct your XML using string concatenations,
why don't you use FOR XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad747edd8c8b87741135600@.news.microsoft.com ...
> Hello djamilabouzid@.gmail.com,
> Its not all that clear as what is cause a problem here because we don't
> know what reade1.GetString(47) is actually returning.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
oAs the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
oBut After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
oThe problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.
Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.
> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> oAs the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> oBut After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> oThe problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Also instead of trying to construct your XML using string concatenations,
why don't you use FOR XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad747edd8c8b87741135600@.news.microsoft.com ...
> Hello djamilabouzid@.gmail.com,
> Its not all that clear as what is cause a problem here because we don't
> know what reade1.GetString(47) is actually returning.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
ASP.Net, Microsoft SQL Server 2005 and XML
Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
o As the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
o But After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
o The problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.
> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> o As the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> o But After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> o The problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
o As the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
o But After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
o The problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.
> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> o As the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> o But After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> o The problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
ASP.NET wont work with otherdatabase than SQLSERVER EXPRESS 2005
I have SQL 2005 developer eedition of SQL server and have a lot of problems because of that.
A lot of features won't work, for example, if I choose App_data folder - add new item and select sqlDatabase I get the following error:
Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the instalation of the component or download
from the URL:http://go.microsoft.com/fwlink/?LinkId=49251
The reason is that LocalSqlServer is defined in machine config file and also on some other places as sql express 2005. How can I change that?
I tried everything but with no success.
Does anybody know how to work with ASP.NET 2.0 and SQL developer version 2005 instead of express edition?
Thanks,S
Follow the steps in this thread and post again if you still need help.
http://forums.asp.net/1231958/ShowPost.aspx
|||Hello. I have the exact same problem, and I can't solve it doing what you sugested on the other thread...
What do you mean by adding a blank Database? Just create a new database with any name, and leave it there?
And what do you mean by "To connect go to VS and click to datalink property and you should be able to connect."? Where is this datalink property? Thanks in advance :)
|||Yes create a database and the datalink property is at the top of VS2003/5 or you can just use Northwind or Pubs the sample databases in SQL Server. Hope this helps.
|||It won't work.
The procedure is:
first open aspnet_regsql tool and database with the name:aspnetdb will be created automatically on the server which you sprecify. For now, everything is ok. This database is needed by asp.net for profiles, web parts,... and account which asp.net is running at, should be the owner of that database(I think).
Then you must change the localSqlServer connection string which is defined in ASP.Net configuration settings. Default is:
data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
I change it to:
Data Source=./NL-SI-1049;Initial Catalog=aspnetdb;Integrated Security=SSPI;
Now I get the following error message:
An error has occurred while establishing a connectiontothe server. When connectingto SQL Server 2005, this failure may be caused bythe fact that underthe default settings SQL Serverdoesnot allow remote connections. (provider: Named Pipes Provider, error: 40 - Couldnot open a connectionto SQL Server)
Well, my setting allowsremote connections, so that can't be the reason. I have enabled it on both TCP/IP and named pipes and I also restart server after enabling them.
I belive, that this has something to do with "User Instance=true" but not shure.
Anybody know how to set ASP.NET2.0 to work with other server than sqlexpress?
It drives me crazy. I'm looking everywhere but I can't find the solution.
I have already installed sql2000 desktop edition and sql2005 developer edition and I don't want't to install also SQL2005 express just because of ASP.NET2.0. It's nonsense.
Regards,S
|||When I got the remote error it was related to SQL Server service in SQL Server 2000 off, so check all your SQL Server service. And I don't think you need SQL Server Express when you are running the developer edition. Hope this helps.ASP.NET w/ SQL Server Sessions & Cluster Failover
Is there an elegant way to clear out the ADO connection pool used by the SQL
Server State Service following a cluster failover?
We are using a SQL Cluster to store both the ASP Session State database and
our application database.
When the SQL Server cluster is failed-over, any "pointers" to the live
connections stored in the connection pool are made invalid. When the
application tries to reference these "damaged" connections, it throws an
exception, and the pooling mechanism drops the bad connections from the
pool.
In our application, we are using a looping try/catch block to clear out the
bad connections from the pool following a failover, and the users typically
are not affected by the failover as a result of any exceptions in our
application code.
However, we don't have access to the source code for managing the ASPState
connections. It appears as though there is no mechanism for the ASPState
database to clear out the bad connections from the pool following a
failover, and thus the exceptions are affecting users.
Is SQL Server Session State Management even supposed to work with a SQL
Cluster?
Mike Olund
OpenRoad Communications
Same thing happens to us. We use ASP.Net connection pools very heavily and
they have some issues with bad connections after a failover. We also use a
try/catch to reconnect if there is a bad connection. If you find anything
else, please let me know.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>
|||there is support in asp.net 2.0, the supported method in asp.net 1.1 is to
change the connection string (thus not reusing any old connections).
if you want an unsupported method, you can use reflection to call an
undocumented method.
(http://www.sys-con.com/dotnet/article.cfm?id=483)
-- bruce (sqlwork.com)
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>
Server State Service following a cluster failover?
We are using a SQL Cluster to store both the ASP Session State database and
our application database.
When the SQL Server cluster is failed-over, any "pointers" to the live
connections stored in the connection pool are made invalid. When the
application tries to reference these "damaged" connections, it throws an
exception, and the pooling mechanism drops the bad connections from the
pool.
In our application, we are using a looping try/catch block to clear out the
bad connections from the pool following a failover, and the users typically
are not affected by the failover as a result of any exceptions in our
application code.
However, we don't have access to the source code for managing the ASPState
connections. It appears as though there is no mechanism for the ASPState
database to clear out the bad connections from the pool following a
failover, and thus the exceptions are affecting users.
Is SQL Server Session State Management even supposed to work with a SQL
Cluster?
Mike Olund
OpenRoad Communications
Same thing happens to us. We use ASP.Net connection pools very heavily and
they have some issues with bad connections after a failover. We also use a
try/catch to reconnect if there is a bad connection. If you find anything
else, please let me know.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>
|||there is support in asp.net 2.0, the supported method in asp.net 1.1 is to
change the connection string (thus not reusing any old connections).
if you want an unsupported method, you can use reflection to call an
undocumented method.
(http://www.sys-con.com/dotnet/article.cfm?id=483)
-- bruce (sqlwork.com)
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>
Monday, February 13, 2012
Asp.net queries with sqlserver2000
Sir,i am running .net1.1 on my system with sqlserver as my database.I am devaloping an e-trading project in
ASP.NET with VB.net.My problem is that,I am not getting the correct syntax to query the database with
SELECT(with WHERE clause)in vb.net.
Please help me.Please show your code and give the EXACT details of the error, inclusing message.
ASP.NET with VB.net.My problem is that,I am not getting the correct syntax to query the database with
SELECT(with WHERE clause)in vb.net.
Please help me.Please show your code and give the EXACT details of the error, inclusing message.
Asp.Net not finding the SQLServer for setting up Security problem
I have just reciently installed and started upgrading the last beta code to this beta and am having a problem conecting to my sqlinstance with the WebSite Configuration Tool.
The error indicates it is looking for a file in the app_data directory. (empty), but everthing else points to SQLServer (express) as being the issue.
I have run aspnet_regsql.exe (both on my default instance of sql (a sql2kas) and the beta2 instance that shipped with VS.
Both created datasases and eveyting looked good inside the db's.
However when I click the "Securtiy tab" I get a message "Unable to connect to SQL Server Database."
When I go to The Provider Tab, there is nothing to configure, just a test Hlink that generates the following when clicked.
SQLExpress database file auto-creation error:
The connection string specifies a local SQL Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The .......
I have run SQL Trace (profiler) against both instances of SQL when aps is trying to connect, but neither show anything.
I keep feeling like I must have missed something.. Anyu clues are welcome.
Rob
This I fixed by editing the Machine.Config file and changing the Connection String for the Database Access. (Can't remember just which one, but if you do a search for SQLExpress in teh machine.config you will find it.)
HTH
Rob
1) Create a database in SQL Server (2000 or 2005) and make sure that you give the ASPNET account permissions to this database.
2) Run the aspnet_regsql.exe file in your System%Root\Microsoft.NET\Framework\v2.0.xyz directory. This will open an ASP.NET SQL Server Setup Wizard which will create the objects necessary for ASP.NET security.
3) Point to the database you just created.
4) In your web.config file, locate the <connectionStrings> element and add the following:
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=localhost;initial catalog=<your database name>;integrated security=true" providerName="System.Data.SqlClient">
This overrides the default SQL Express and points to the new database you just created - be it in the same machine or a different machine. You can also change the configuration in the machine.config file although this will affect every web application sitting on top of your machine.|||WOW! it did solve my problem. thanks so much
Worked !
Thanks a lot.
(I can now start growing my hair back 
Bill
|||I have the same problem :S
<connectionStrings>
<remove name="LocalSqlServer" />
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;
integrated security=true " providerName="System.Data.SqlClient">
</connectionStrings>
i still get error
Error 4 XML document cannot contain multiple root level elements.
:S:S
<connectionStrings>
<remove name="LocalSqlServer" />
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;
integrated security=true " providerName="System.Data.SqlClient">
</connectionStrings>
I think your error might be pointing to the Add tag as it is missing the Close Tag. However you seem to have an XML issue, not the issue that started the thread..
Rob
E.G. (highlighted in red)
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;integrated security=true " providerName="System.Data.SqlClient" />
|||Is it mandatory to have SQL Server for accessing the security features like creating users and roles in ASP.Net Configuration tool? If not please tell me the way to avoid the same problem...
Asp.Net not finding the SQLServer for setting up Security problem
I have just reciently installed and started upgrading the last beta code to this beta and am having a problem conecting to my sqlinstance with the WebSite Configuration Tool.
The error indicates it is looking for a file in the app_data directory. (empty), but everthing else points to SQLServer (express) as being the issue.
I have run aspnet_regsql.exe (both on my default instance of sql (a sql2kas) and the beta2 instance that shipped with VS.
Both created datasases and eveyting looked good inside the db's.
However when I click the "Securtiy tab" I get a message "Unable to connect to SQL Server Database."
When I go to The Provider Tab, there is nothing to configure, just a test Hlink that generates the following when clicked.
SQLExpress database file auto-creation error:
The connection string specifies a local SQL Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The .......
I have run SQL Trace (profiler) against both instances of SQL when aps is trying to connect, but neither show anything.
I keep feeling like I must have missed something.. Anyu clues are welcome.
Rob
This I fixed by editing the Machine.Config file and changing the Connection String for the Database Access. (Can't remember just which one, but if you do a search for SQLExpress in teh machine.config you will find it.)
HTH
Rob
1) Create a database in SQL Server (2000 or 2005) and make sure that you give the ASPNET account permissions to this database.
2) Run the aspnet_regsql.exe file in your System%Root\Microsoft.NET\Framework\v2.0.xyz directory. This will open an ASP.NET SQL Server Setup Wizard which will create the objects necessary for ASP.NET security.
3) Point to the database you just created.
4) In your web.config file, locate the <connectionStrings> element and add the following:
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=localhost;initial catalog=<your database name>;integrated security=true" providerName="System.Data.SqlClient">
This overrides the default SQL Express and points to the new database you just created - be it in the same machine or a different machine. You can also change the configuration in the machine.config file although this will affect every web application sitting on top of your machine.|||WOW! it did solve my problem. thanks so much
Worked !
Thanks a lot.
(I can now start growing my hair back
Bill
|||I have the same problem :S
<connectionStrings>
<remove name="LocalSqlServer" />
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;
integrated security=true " providerName="System.Data.SqlClient">
</connectionStrings>
i still get error
Error 4 XML document cannot contain multiple root level elements.
:S:S
<connectionStrings>
<remove name="LocalSqlServer" />
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;
integrated security=true " providerName="System.Data.SqlClient">
</connectionStrings>
I think your error might be pointing to the Add tag as it is missing the Close Tag. However you seem to have an XML issue, not the issue that started the thread..
Rob
E.G. (highlighted in red)
<add name ="LocalSqlServer" connectionString="Data Source=localhost;initial catalog = memberDB;integrated security=true " providerName="System.Data.SqlClient" />
|||Is it mandatory to have SQL Server for accessing the security features like creating users and roles in ASP.Net Configuration tool? If not please tell me the way to avoid the same problem...
Subscribe to:
Posts (Atom)