we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total memor
y
but our front end tool is PB .often our application close cause of locking
at a time more than 8 users executing sp_xml_preparedocument what will
happen , i need urgent soultionMake sure you are using sp_xml_removedocument
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Subramaniyan Ramesh" <SubramaniyanRamesh@.discussions.microsoft.com> wrote
in message news:29D5FD50-CA2B-4090-B589-0749BC460200@.microsoft.com...
> we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total
> memory
> but our front end tool is PB .often our application close cause of locking
> at a time more than 8 users executing sp_xml_preparedocument what will
> happen , i need urgent soultion
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 22, 2012
Asynchrous query
I have a query that because the database is across the country it takes time
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes time
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be asynchronous.
If you use any of above mentioned methods, you cannot read off of the results until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
have to wait for the whole set until you can present data to the end user. Note that SQL Server can
use worktables and don't send the result until it is done, depending on the execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes radically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
of rows.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes time
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most of
> the delay is network related but it is hard to tell how close to finishing a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The data
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML. I
would however like to use ExecuteXmlReader but I get an exception indicating
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be asynchronous.
> If you use any of above mentioned methods, you cannot read off of the results until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
> have to wait for the whole set until you can present data to the end user. Note that SQL Server can
> use worktables and don't send the result until it is done, depending on the execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
> >I have a query that because the database is across the country it takes time
> > to bring a large amount of data to the client executing a command. I was
> > wondering if there was a way to execute an asychronus command? Ideally I
> > would like to get periodic feedback on the progress. I am sure that most of
> > the delay is network related but it is hard to tell how close to finishing a
> > command is by just executing the ExecuteScalar from a SQL command.
> >
> > Thank you for your suggestions.
> >
> > Kevin
>|||That is a bit too high in the API stack for me to be able to comment on. Perhaps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.NET group, unless someone
jump in here with more specific recommendations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...
>I started to look at these calls and it brought up another question. The data
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML. I
> would however like to use ExecuteXmlReader but I get an exception indicating
> that the data must be XML. How do I convince the client that it is XML data?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML document
> (specifically an XPath document because I want to do some XPath searches on
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc.
>> Doesn't
>> seem to be a BeginExecuteScalar, though.
>> But you should carefully consider in what way you want the execution to be asynchronous.
>> If you use any of above mentioned methods, you cannot read off of the results until the whole set
>> has been passed to the client.
>> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
>> have to wait for the whole set until you can present data to the end user. Note that SQL Server
>> can
>> use worktables and don't send the result until it is done, depending on the execution plan. A
>> FAST
>> hint can be useful, but that can increase the total query cost (sometimes radically).
>> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a
>> number
>> of rows.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
>> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>> >I have a query that because the database is across the country it takes time
>> > to bring a large amount of data to the client executing a command. I was
>> > wondering if there was a way to execute an asychronus command? Ideally I
>> > would like to get periodic feedback on the progress. I am sure that most of
>> > the delay is network related but it is hard to tell how close to finishing a
>> > command is by just executing the ExecuteScalar from a SQL command.
>> >
>> > Thank you for your suggestions.
>> >
>> > Kevin
>>
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes time
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be asynchronous.
If you use any of above mentioned methods, you cannot read off of the results until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
have to wait for the whole set until you can present data to the end user. Note that SQL Server can
use worktables and don't send the result until it is done, depending on the execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes radically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
of rows.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes time
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most of
> the delay is network related but it is hard to tell how close to finishing a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The data
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML. I
would however like to use ExecuteXmlReader but I get an exception indicating
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be asynchronous.
> If you use any of above mentioned methods, you cannot read off of the results until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
> have to wait for the whole set until you can present data to the end user. Note that SQL Server can
> use worktables and don't send the result until it is done, depending on the execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
> >I have a query that because the database is across the country it takes time
> > to bring a large amount of data to the client executing a command. I was
> > wondering if there was a way to execute an asychronus command? Ideally I
> > would like to get periodic feedback on the progress. I am sure that most of
> > the delay is network related but it is hard to tell how close to finishing a
> > command is by just executing the ExecuteScalar from a SQL command.
> >
> > Thank you for your suggestions.
> >
> > Kevin
>|||That is a bit too high in the API stack for me to be able to comment on. Perhaps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.NET group, unless someone
jump in here with more specific recommendations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...
>I started to look at these calls and it brought up another question. The data
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML. I
> would however like to use ExecuteXmlReader but I get an exception indicating
> that the data must be XML. How do I convince the client that it is XML data?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML document
> (specifically an XPath document because I want to do some XPath searches on
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc.
>> Doesn't
>> seem to be a BeginExecuteScalar, though.
>> But you should carefully consider in what way you want the execution to be asynchronous.
>> If you use any of above mentioned methods, you cannot read off of the results until the whole set
>> has been passed to the client.
>> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
>> have to wait for the whole set until you can present data to the end user. Note that SQL Server
>> can
>> use worktables and don't send the result until it is done, depending on the execution plan. A
>> FAST
>> hint can be useful, but that can increase the total query cost (sometimes radically).
>> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a
>> number
>> of rows.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
>> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>> >I have a query that because the database is across the country it takes time
>> > to bring a large amount of data to the client executing a command. I was
>> > wondering if there was a way to execute an asychronus command? Ideally I
>> > would like to get periodic feedback on the progress. I am sure that most of
>> > the delay is network related but it is hard to tell how close to finishing a
>> > command is by just executing the ExecuteScalar from a SQL command.
>> >
>> > Thank you for your suggestions.
>> >
>> > Kevin
>>
Asynchronous data flow tasks how to run more than 4 at a time
Hi guys,
i have a for each loop and it has about 20 data flow tasks (simple data extractions). i notice when i run the package it only runs up to 4 data flow tasks at a time. others have to wait till one of the first 4 flows finishes.
i was wondering if there's a way to change the limit of how many data flow tasks can run at a time. is there a property some where ?
i know this will be stressfull to the server, but the server is well equiped with CPU power and memory, so performance will not be an issue.
any thoughts?
Package.MaxConcurrentExecutables Property
Valid values are one and higher, or -1. Other values are invalid. A value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument.
This property is used when parallelism exists in the workflow. If the workflow is a series of sequential precedence constraints, then this property has no effect.
I don't know if you can get more than CPU Count + 2 by forcing the value. If this is a 32-bit server then I would be concerned about memory, as despite having 10 GB in there, a process (read SSIS Package) can only use 2GB or 3GB with the/3GB boot.ini switch, so you may want to break out into multiple packages, or just call the same package multiple times. The Execute Package Task can be used to get multiple processes with the out of processes property, but this has a higher overhead for loading and starting the packages.
Asynchronous Batch Processing
Currently we have a process that does synchronous batch processing that can
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon
ASYNC_NETWORK_IO issue
I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?
SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.
Thanks
-Ashis
Hi, Ashis,
Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.
Thanks,
Zhiqiang Feng
Monday, March 19, 2012
assistance please....
What is the easiest way to check the date and time of service pack updates?
What do you mean by that `?
Thursday, March 8, 2012
assigning DB-user to server role
Hello NG,
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
via
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
quote:
> Hello NG,
> each time we assign users to roles, we have to additionally grant each
> access right of each function of each table manually to this new user. Of
> course we'd expect this to be done by the assignment itself.
> Either we don't understand the servers's security philosophy correctly or
> there should be another procedure to grant users access to the database
via
quote:|||You script seems to promise what we expected when we used the Enterprise
> the role, which has already defined access rights.
> Thankful for any hint ...
> Marc
>
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
quote:|||You should be able to do these same steps using EM as well. I find that SQL
> You should be able to grant object permissions to the roles only. Member
> users will inherit permissions via role membership. If a user is a member
> of multiple roles, granted permissions are cumulative. However, denied
> permissions take precedence.
> I'm not sure how you've setup your users but the script below illustrates
> this technique.
> USE MyDatabase
> --setup role security
> EXEC sp_addrole 'MyRole'
> GRANT SELECT ON MyTable TO MyRole
> --setup user security
> EXEC sp_grantlogin 'MyDomain\MyUser'
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
> news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
Of[QUOTE]
or[QUOTE]
> via
>
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...
quote:|||Perhaps if you used profiler while you recreate your scenario in EM (create
> You script seems to promise what we expected when we used the Enterprise
> Manager to assign users to roles. Why does it work through scripts and not
> through "klick-it"?
> Marc
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
> news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
Member[QUOTE]
member[QUOTE]
illustrates[QUOTE]
> Of
> or
database[QUOTE]
>
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Assigning a lower priority to some users in SQL Server.
I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”
When one of the users runs a complex query, it slows down the server, and affects the web site.
Is it possible to change the SQL User account or SQL User Group’s priority to low?
You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.
in my humble opinion
its the query that needs to be changed
or if your using 2005 you can implement HA feature
such as database snapshot, mirroring etc.
|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||
if you are so convince that thats the best query you can write and there is no
room for improvement then you can schedule your heavy process to run
on offpeak times.
if that heavy process cross the line of tolerable performance your only option
is to kill that process.
the solution to your problem are
1. send readonly report users to a database snapshot if you are using 2k5
2. schedule the process to run on offpeak times
3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database
By the way, what does this complex query do? if you would not mind.
how complex is it?
Wednesday, March 7, 2012
Assign multiple groups to role(s)
I have not seen this question pose anywhere.
Currently we can only assign 1 group at a time to role(s).
Has anyone found a way to assign more than 1 group at a time to role(s)?
I have about 70+ groups to assign role(s) to and doing this one by one is
just not going to work.
I'm using Custom Authentication. Although it has nothing to do with this.
I'm just curious as to what Microsoft plans on doing about this. In the mean
time how is everyone dealing with this issue? How anyone found a way to do it?
Thanks.I do the following. I have a local group. I then assign all my domain groups
to that local group. I only assign a single group to the role(the one local
group). Any changes to membership (for instance adding a particular user or
adding another group) is all done in Windows, nothing changes with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jon-Slem" <JonSlem@.discussions.microsoft.com> wrote in message
news:09714ACE-C939-4903-8981-ABAF7EB0A681@.microsoft.com...
> I have not seen this question pose anywhere.
> Currently we can only assign 1 group at a time to role(s).
> Has anyone found a way to assign more than 1 group at a time to role(s)?
> I have about 70+ groups to assign role(s) to and doing this one by one is
> just not going to work.
> I'm using Custom Authentication. Although it has nothing to do with this.
> I'm just curious as to what Microsoft plans on doing about this. In the
mean
> time how is everyone dealing with this issue? How anyone found a way to do
it?
> Thanks.
Currently we can only assign 1 group at a time to role(s).
Has anyone found a way to assign more than 1 group at a time to role(s)?
I have about 70+ groups to assign role(s) to and doing this one by one is
just not going to work.
I'm using Custom Authentication. Although it has nothing to do with this.
I'm just curious as to what Microsoft plans on doing about this. In the mean
time how is everyone dealing with this issue? How anyone found a way to do it?
Thanks.I do the following. I have a local group. I then assign all my domain groups
to that local group. I only assign a single group to the role(the one local
group). Any changes to membership (for instance adding a particular user or
adding another group) is all done in Windows, nothing changes with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jon-Slem" <JonSlem@.discussions.microsoft.com> wrote in message
news:09714ACE-C939-4903-8981-ABAF7EB0A681@.microsoft.com...
> I have not seen this question pose anywhere.
> Currently we can only assign 1 group at a time to role(s).
> Has anyone found a way to assign more than 1 group at a time to role(s)?
> I have about 70+ groups to assign role(s) to and doing this one by one is
> just not going to work.
> I'm using Custom Authentication. Although it has nothing to do with this.
> I'm just curious as to what Microsoft plans on doing about this. In the
mean
> time how is everyone dealing with this issue? How anyone found a way to do
it?
> Thanks.
Assertion Errors:
Hi All,
I have started getting Assertion Errors in SQL.
It appears when I process a cube (Most of the time)
Other SQL statements, usually with a join or 6 do the same thing.
Whaving a scratch around google, I noticed the most people who get
these errors are using SATA drives. Either RAID or not.
Surprise, I am using SATA in RAID 1.
Is this a common thing with SATA? I can't go to the pwers that be and
say I need a couple large SCSI drives because I _think_ it's the
SATA's.
Another very odd thing that happened thismorning was I copied the mdf
and ldf files off my machine (About 70GB) and onto the server. attached
them and SQL was happy.
Select Count(*) from aview gave me the count I was expecting.
Select * From aview returned no rows. most of the time.
I thought I was going mad. F5 works, then it doesn't then it does then,
you get the point.
Backup and restore seemed better until the errors below started...
HELP!!!!
Thanks
Cheers,
Crispin
17066 :
SQL Server Assertion: File:
<q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.<crispin.proctor@.gmail.com> wrote in message
news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> Hi All,
> I have started getting Assertion Errors in SQL.
> It appears when I process a cube (Most of the time)
> Other SQL statements, usually with a join or 6 do the same thing.
> Whaving a scratch around google, I noticed the most people who get
> these errors are using SATA drives. Either RAID or not.
> Surprise, I am using SATA in RAID 1.
> Is this a common thing with SATA? I can't go to the pwers that be and
> say I need a couple large SCSI drives because I _think_ it's the
> SATA's.
> Another very odd thing that happened thismorning was I copied the mdf
> and ldf files off my machine (About 70GB) and onto the server. attached
> them and SQL was happy.
> Select Count(*) from aview gave me the count I was expecting.
> Select * From aview returned no rows. most of the time.
> I thought I was going mad. F5 works, then it doesn't then it does then,
> you get the point.
> Backup and restore seemed better until the errors below started...
>
> HELP!!!!
> Thanks
> Cheers,
> Crispin
> 17066 :
> SQL Server Assertion: File:
> <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
This KB article seems to describe what you're seeing:
http://support.microsoft.com/kb/828337
Simon|||Thanks Simon,
Problem is the checkDB does the same thing.
We have replaced the drives, drive controller etc.
Even replaced the mother board.
Only thing I have not replaced on the machine is the mouse :)
My concern is we have shipped some servers to client with a simlar
config. (SATA drives running RAID 1)
Now if this is a limitation of SATA I am in a world of sh1t.
We use SATA drives as a cheaper alternative to SCSI for some clients.
I reasoned they would be faster than IDE and if Dell, HP etc have SANs
running on SATA drives, they can't be all that bad.
arrrrggggggg. This is killing me. It's actually gettiing worse. Run the
diagnostics on the drives and no error's reported.
Cheers,
Crispin
Simon Hayes wrote:
> <crispin.proctor@.gmail.com> wrote in message
> news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> > Hi All,
> > I have started getting Assertion Errors in SQL.
> > It appears when I process a cube (Most of the time)
> > Other SQL statements, usually with a join or 6 do the same thing.
> > Whaving a scratch around google, I noticed the most people who get
> > these errors are using SATA drives. Either RAID or not.
> > Surprise, I am using SATA in RAID 1.
> > Is this a common thing with SATA? I can't go to the pwers that be
and
> > say I need a couple large SCSI drives because I _think_ it's the
> > SATA's.
> > Another very odd thing that happened thismorning was I copied the
mdf
> > and ldf files off my machine (About 70GB) and onto the server.
attached
> > them and SQL was happy.
> > Select Count(*) from aview gave me the count I was expecting.
> > Select * From aview returned no rows. most of the time.
> > I thought I was going mad. F5 works, then it doesn't then it does
then,
> > you get the point.
> > Backup and restore seemed better until the errors below started...
> > HELP!!!!
> > Thanks
> > Cheers,
> > Crispin
> > 17066 :
> > SQL Server Assertion: File:
> > <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> > Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> This KB article seems to describe what you're seeing:
> http://support.microsoft.com/kb/828337
> Simon|||(crispin.proctor@.gmail.com) writes:
> Problem is the checkDB does the same thing.
> We have replaced the drives, drive controller etc.
> Even replaced the mother board.
> Only thing I have not replaced on the machine is the mouse :)
> My concern is we have shipped some servers to client with a simlar
> config. (SATA drives running RAID 1)
> Now if this is a limitation of SATA I am in a world of sh1t.
> We use SATA drives as a cheaper alternative to SCSI for some clients.
> I reasoned they would be faster than IDE and if Dell, HP etc have SANs
> running on SATA drives, they can't be all that bad.
The KB article that Simon referred you to, suggested that you should
open a case with Microsoft, and I would encourage you do that.
Assertion errors are always bugs in SQL Server in the sense that they
should not occur. But it could be that instead you should have gotten
a better error message. In your case, it appears that your database is
corrupt. (Which could be due to a previous hardware error.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Assertion Errors, temdb guest account, and other errors
Last night, we had a sql server (2000, sp3) whose transaction logs get
backed up to a share have the log backup jobs fail. At the same time, a
database that is a replication publisher became unavailable. We were getting
the following errors in the sql server error log but this error was written
only once to the W2003 Application event log:
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <sysscan.cpp>, line=565
Failed Assertion = 'xdes->GetDbId () == sDbid'.
I'm still researching this error.
The replicated database showed up in EM but there were no tables (not even
system tables). We tried to detach it to see if we could perhaps re-attach
the files and return it to availability but since it was being replicated,
it would not allow us to do so. We tried various ways of dropping the
publication but since the database was unavailable, we were unable to drop
it.
Since regardless of our resolution to bringing this database back on-line
(restore, etc.) was going to require that we get rid of it as it is, we
restarted the sql server service. The database showed up again and seemed to
be fine. However, this morning, after numerous reports from people unable to
see the databases on this server and errors related to invalid login in
tempdb, I discovered that the guest account was disabled. I granted access
to tempdb to the guest account and that resolved this problem.
We have also had a number of problems with the linked servers that were
previously functional to and from this server.
We recreated the replication of the database that had the problem last night
and the conflict tables are named aonflict% instead of conflict%. We're not
sure if this is related or not but the other problems certainly seem to be
related.
Everything seems to be functional now but I am faced with the task of
putting all of these pieces together in order to determine the cause and to
prevent it from happening again. Any advice on any of these possibly related
issues would be appreciated.It appears as if guest was disabled in model which would explain why it was
disabled in tempdb after restarting the sql server service. I'm not sure why
it was disabled in model.
We're calling MS PSS regarding the assertion errors.
It appears as if the funny conflict table names are due to the failure to
clean up the old publication and/or subscription.
"michelle" <michelle@.nospam.com> wrote in message
news:uUP169kdEHA.1604@.TK2MSFTNGP11.phx.gbl...
> Last night, we had a sql server (2000, sp3) whose transaction logs get
> backed up to a share have the log backup jobs fail. At the same time, a
> database that is a replication publisher became unavailable. We were
getting
> the following errors in the sql server error log but this error was
written
> only once to the W2003 Application event log:
> Error: 3624, Severity: 20, State: 1.
> SQL Server Assertion: File: <sysscan.cpp>, line=565
> Failed Assertion = 'xdes->GetDbId () == sDbid'.
> I'm still researching this error.
> The replicated database showed up in EM but there were no tables (not even
> system tables). We tried to detach it to see if we could perhaps re-attach
> the files and return it to availability but since it was being replicated,
> it would not allow us to do so. We tried various ways of dropping the
> publication but since the database was unavailable, we were unable to drop
> it.
> Since regardless of our resolution to bringing this database back on-line
> (restore, etc.) was going to require that we get rid of it as it is, we
> restarted the sql server service. The database showed up again and seemed
to
> be fine. However, this morning, after numerous reports from people unable
to
> see the databases on this server and errors related to invalid login in
> tempdb, I discovered that the guest account was disabled. I granted access
> to tempdb to the guest account and that resolved this problem.
> We have also had a number of problems with the linked servers that were
> previously functional to and from this server.
> We recreated the replication of the database that had the problem last
night
> and the conflict tables are named aonflict% instead of conflict%. We're
not
> sure if this is related or not but the other problems certainly seem to be
> related.
> Everything seems to be functional now but I am faced with the task of
> putting all of these pieces together in order to determine the cause and
to
> prevent it from happening again. Any advice on any of these possibly
related
> issues would be appreciated.
>
backed up to a share have the log backup jobs fail. At the same time, a
database that is a replication publisher became unavailable. We were getting
the following errors in the sql server error log but this error was written
only once to the W2003 Application event log:
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <sysscan.cpp>, line=565
Failed Assertion = 'xdes->GetDbId () == sDbid'.
I'm still researching this error.
The replicated database showed up in EM but there were no tables (not even
system tables). We tried to detach it to see if we could perhaps re-attach
the files and return it to availability but since it was being replicated,
it would not allow us to do so. We tried various ways of dropping the
publication but since the database was unavailable, we were unable to drop
it.
Since regardless of our resolution to bringing this database back on-line
(restore, etc.) was going to require that we get rid of it as it is, we
restarted the sql server service. The database showed up again and seemed to
be fine. However, this morning, after numerous reports from people unable to
see the databases on this server and errors related to invalid login in
tempdb, I discovered that the guest account was disabled. I granted access
to tempdb to the guest account and that resolved this problem.
We have also had a number of problems with the linked servers that were
previously functional to and from this server.
We recreated the replication of the database that had the problem last night
and the conflict tables are named aonflict% instead of conflict%. We're not
sure if this is related or not but the other problems certainly seem to be
related.
Everything seems to be functional now but I am faced with the task of
putting all of these pieces together in order to determine the cause and to
prevent it from happening again. Any advice on any of these possibly related
issues would be appreciated.It appears as if guest was disabled in model which would explain why it was
disabled in tempdb after restarting the sql server service. I'm not sure why
it was disabled in model.
We're calling MS PSS regarding the assertion errors.
It appears as if the funny conflict table names are due to the failure to
clean up the old publication and/or subscription.
"michelle" <michelle@.nospam.com> wrote in message
news:uUP169kdEHA.1604@.TK2MSFTNGP11.phx.gbl...
> Last night, we had a sql server (2000, sp3) whose transaction logs get
> backed up to a share have the log backup jobs fail. At the same time, a
> database that is a replication publisher became unavailable. We were
getting
> the following errors in the sql server error log but this error was
written
> only once to the W2003 Application event log:
> Error: 3624, Severity: 20, State: 1.
> SQL Server Assertion: File: <sysscan.cpp>, line=565
> Failed Assertion = 'xdes->GetDbId () == sDbid'.
> I'm still researching this error.
> The replicated database showed up in EM but there were no tables (not even
> system tables). We tried to detach it to see if we could perhaps re-attach
> the files and return it to availability but since it was being replicated,
> it would not allow us to do so. We tried various ways of dropping the
> publication but since the database was unavailable, we were unable to drop
> it.
> Since regardless of our resolution to bringing this database back on-line
> (restore, etc.) was going to require that we get rid of it as it is, we
> restarted the sql server service. The database showed up again and seemed
to
> be fine. However, this morning, after numerous reports from people unable
to
> see the databases on this server and errors related to invalid login in
> tempdb, I discovered that the guest account was disabled. I granted access
> to tempdb to the guest account and that resolved this problem.
> We have also had a number of problems with the linked servers that were
> previously functional to and from this server.
> We recreated the replication of the database that had the problem last
night
> and the conflict tables are named aonflict% instead of conflict%. We're
not
> sure if this is related or not but the other problems certainly seem to be
> related.
> Everything seems to be functional now but I am faced with the task of
> putting all of these pieces together in order to determine the cause and
to
> prevent it from happening again. Any advice on any of these possibly
related
> issues would be appreciated.
>
Saturday, February 25, 2012
Assert Permission - Please Look into It
Hi All,
We are trying to find the solution for this for a very long time, hope I
can get solution for this thread.
This dll code is used to decryt the CreditCardNumber, This is working when
I use it in a web application, But I need to know what assert Permission I
need to Give to get the reult...
using System;
using System.Text;
using System.Net;
using System.IO;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Permissions;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace CreditCardDecrypt
{
/// <summary>
/// Summary description for DecryptClass.
/// </summary>
///
public class DecryptClass
{
public DecryptClass()
{
//
// TODO: Add constructor logic here
//
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
string sCreditCard = "";
try
{
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
//return cc;
}
catch(Exception ex)
{
return ex.ToString();
}
}
}
}
--
Message posted via http://www.sqlmonster.comWhat's the exception you get when you run the code.
It should tell you which permission needs to be asserted.
Or you can assert full trust which is less favorable due to security
reasons:
[PermissionSet(SecurityAction.Demand, Name="FullTrust")]
"BALAJI via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:e70202f5043c49539b30bf614b8a6377@.SQLMonster.com...
> Hi All,
> We are trying to find the solution for this for a very long time, hope I
> can get solution for this thread.
> This dll code is used to decryt the CreditCardNumber, This is working when
> I use it in a web application, But I need to know what assert Permission I
> need to Give to get the reult...
> using System;
> using System.Text;
> using System.Net;
> using System.IO;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Permissions;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace CreditCardDecrypt
> {
> /// <summary>
> /// Summary description for DecryptClass.
> /// </summary>
> ///
> public class DecryptClass
> {
> public DecryptClass()
> {
> //
> // TODO: Add constructor logic here
> //
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> string sCreditCard = "";
> try
> {
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> //return cc;
> }
> catch(Exception ex)
> {
> return ex.ToString();
> }
> }
> }
> }
> --
> Message posted via http://www.sqlmonster.com
We are trying to find the solution for this for a very long time, hope I
can get solution for this thread.
This dll code is used to decryt the CreditCardNumber, This is working when
I use it in a web application, But I need to know what assert Permission I
need to Give to get the reult...
using System;
using System.Text;
using System.Net;
using System.IO;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Permissions;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace CreditCardDecrypt
{
/// <summary>
/// Summary description for DecryptClass.
/// </summary>
///
public class DecryptClass
{
public DecryptClass()
{
//
// TODO: Add constructor logic here
//
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
string sCreditCard = "";
try
{
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
//return cc;
}
catch(Exception ex)
{
return ex.ToString();
}
}
}
}
--
Message posted via http://www.sqlmonster.comWhat's the exception you get when you run the code.
It should tell you which permission needs to be asserted.
Or you can assert full trust which is less favorable due to security
reasons:
[PermissionSet(SecurityAction.Demand, Name="FullTrust")]
"BALAJI via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:e70202f5043c49539b30bf614b8a6377@.SQLMonster.com...
> Hi All,
> We are trying to find the solution for this for a very long time, hope I
> can get solution for this thread.
> This dll code is used to decryt the CreditCardNumber, This is working when
> I use it in a web application, But I need to know what assert Permission I
> need to Give to get the reult...
> using System;
> using System.Text;
> using System.Net;
> using System.IO;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Permissions;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace CreditCardDecrypt
> {
> /// <summary>
> /// Summary description for DecryptClass.
> /// </summary>
> ///
> public class DecryptClass
> {
> public DecryptClass()
> {
> //
> // TODO: Add constructor logic here
> //
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> string sCreditCard = "";
> try
> {
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> //return cc;
> }
> catch(Exception ex)
> {
> return ex.ToString();
> }
> }
> }
> }
> --
> Message posted via http://www.sqlmonster.com
Sunday, February 19, 2012
asp:login / ASP.NET Configuration menu / & login DB
All,
I may have bitten off a bit much here, but here goes. I'm trying to learn ASP.NET while at the same time learning SQL 2005. I have two machines. One is running Visual Studio 2005 and SQL 2005 Express. The other is my "production" box which is running VWD 2005 and SQL 2005 Standard. I picked up some WROX books and began learning. So far so good until about half way through the book on ASP (keep in mind I haven't learned SQL yet). I learned that I can use the ASP.NET configuration menu to automagically create a database, populate it with users, add them to profiles (groups), and grant them rights to pages. This was kind of neat and it worked fine on my dev box. So I looked up in my SQL book how to detach a DB, copy it over to the other machine and re-attach it. So I copied the whole web site (DB and all, under APP DATA) to the other machine and reattached it. This seemed to go well, however when I tried to log in I recieved an error.
***error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
After doing some hunting around I discovered that I'm supposed to have a connection string. I checked my web.config file and the only thing I find there is
<
appSettings/><
connectionStrings/><
system.web>As you can see this doesn't point to a database which the docs I found say is how you connect to them. Apparently one error someone can run into is between expree and SQL where you have to change the connection string. Well, it's not in the web.config. So I checked the default and login page (and code behind files), but found nothing. So, I'm at a loss. I have no idea where the problem is or where to go from here. Any assistance will be appreciated. While I'm enjoying the journey of learning this, the road seems to be kind of bumpy.
Thanks in advance - Stephen
Hi Stephen,
to change default connection string in web.config you can remove and add new one it's name LocalSqlServer like..
<connectionStrings>
<removename="LocalSqlServer"/>
<addname="LocalSqlServer"connectionString="your connection string..."/>
</connectionStrings>
and give look at this article aboutMembership you will find also how to config Membership
I hope this help
ASP/SQL Query Build - Myth Breaking
Hi Guys
I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:
1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.
Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.
Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.
The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.
I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.
2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?
I really appreciate your help on this.
Regards
LaphanPut your Financial Calendar into a table and join that into your query.
CREATE TABLE Accounts (accountno INTEGER PRIMARY KEY, accountname
VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE FinancialCalendar (yearno INTEGER CHECK (yearno BETWEEN 2000
AND 2100), periodno INTEGER CHECK (periodno BETWEEN 1 AND 12), PRIMARY KEY
(yearno,periodno))
CREATE TABLE Budgets (accountno INTEGER REFERENCES Accounts (accountno),
yearno INTEGER, periodno INTEGER, amount NUMERIC(10,2) NOT NULL, FOREIGN KEY
(yearno,periodno) REFERENCES FinancialCalendar (yearno,periodno))
SELECT A.accountno, A.accountname, C.yearno, C.periodno,
COALESCE(B.amount,0) AS amount
FROM FinancialCalendar AS C
CROSS JOIN Accounts AS A
LEFT JOIN Budgets AS B
ON A.accountno = B.accountno
AND B.yearno = C.yearno
AND B.periodno = C.periodno
WHERE C.yearno BETWEEN 2001 AND 2004
I recommend Celko's SQL Puzzles and Answers if you want to expand your
repertoire of query techniques:
http://tinyurl.com/353dw
--
David Portas
SQL Server MVP
--
Thursday, February 16, 2012
asp.net want to make SQL time format of 00:00:00:000 to 14:42:51:153 when inserting in dat
I have asp.net 1.1 web form and it inserts date and time in SQL database, but it insert only date not time , It insert date time in following format
2002-01-22 00:00:00.000
some one tell me why it is not inserting time or why it is inserting time as 00:00:00:000.
I want to my time to look like 14:42:51:153. (format)
How can I change my time format
give me asp.net codes for time formating or what do I need to do to resolve the problem,
thank you
maxmax
I think your application is not sending the date properly. Do a response.write of the value you are sending to the db and verify.
ASP.NET SQLConnection string problem with ISP "oneandone.co.uk"
Appreciate this is probably one for the oneandone tech support team on Monday, but I'd like to avoid loosing a days development and deployment time by geting this sorted today if possible. Would really appreciate it if anyone can help?
When I try connecting to a SQL 2000 database supplied by my ISP I get an erro telling me the Server doesn't exist or I don't have acces. Am sure the problem lies with my connection string. Only problem is I've tried all possible variations using the "database name", "userid" and "password" supplied by oneandone. Their help pages give the following connection string example:
oConn = New System.Data.SQLClient.SQLConnection ("server=kundenmssql3.schlund.de; initial catalog=Northwind;uid=USERNAME;pwd=PASSWORD")
I've tried using my domain name for the server and the name they've provided in the example, all without any success. I can't find any information on what server name I should be using in my account maintenance area.
Does anyone else out there host with oneandone, and if so, can you point me in the right direction?
Thanks.you may need to specify the network library to use - try the page on this error at www.aspfaq.com
j
asp.net SQL query between given two time
I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time
Can some one show me sample SQL query so I can pull informatin on particular day between two times
thank you
maxmax
http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
|||select *
from sometable
where datefield between '11/2/07' and '11/15/07'
returns all records from 12:00:00 AM on 11/2/07 (that is, just after midnight of 11/1) through and including midnight of 11/15/07
You can add time values very easily:
select *
from sometable
where datefield between '2007-11-1613:30:31.497' and '2007-11-1613:31:05.670'
Sunday, February 12, 2012
asp.net confgiguration tool. tool keeps timing out
is it possible to change the settings on this tool. i want to have it so that it does not time out at all? or is that possible?
I suggest you ask this on the ASP.Net forums
http://forums.asp.net/
Subscribe to:
Posts (Atom)