Concatenating fields: system

Showing posts with label system. Show all posts

Thursday, March 29, 2012

Configure IIS for Reporting Manager and reporting server site

Dear All:

My reporting manager and reporting server were working fine in IIS. But when I booted the system one day, one error message came "Unexpected error" for the IIS. So I removed the IIS and reinstalled he same.

But after that, I tried to give virtual directories to reporting manager and reporting server sites. But it ain't working. Could you please help me?

My IIS 5.1
Reporting service 2005 Express with SQL Server Express
ASP.NET 2.0 Express.

Please help.

Thanks in Advance

Emmanuel MathewDear All:

I got it somehow worked. But now the problem is that when I take the report manager and display the report, it shows me a wrongly formatted page with "Object reference not set to an isntance of an Object"

But in server, even though I am getting the unformatted (Like CSS is not applied), I get the report correctly. Is there anything wrong with the ath of CSS?

And what is this "Object reference error"? Could you please help me.

Thanks
Emmanuel Mathew|||Can you tell us the stack trace in report service log file? Also is there any error in the reporting services configuration tool (not sure if it comes with RS express)?|||Dear Friend:

There is no error screen to take a stack trace. Let me explain. When I give the URL, the reporting manager comes nicely. Then when I click on one of the report, usually, the parameter and the view report button should appear. It appears. But they are all mis aligned. And when Press on the view report, the image which shows the processing rotates and then shows the error in a single line "Object reference not set to an instance of an Object"

When I open the reporting server, I get the directory listing and then when I click on one of the report, the report comes mis aligned as in report manager. But when I click on the view report, it shows the report even though its looking ugly. I get the error only in reporting manager.

Let me tell you what had happened earlier too. One day when I opened the machine, my IIS got an unexpected error and it did not start. SO I reisntalled the same. After reinstalling, I saw that the reporting manager and the server virtual folders are gone. So I created the same and ran the command Reg IIS to register IIS for the ASP.NET 2. I have .NET, sql server and the reporting services which are all express editions installed in the machine. Everything was working fine till I find one day my IIS got corrupted.

Lot of thanks for the support. And thanks in Advance.

Emmanuel Mathew|||I reinstalled IIS and the server stated working fine now. I know its not the solution. But I made it working. Thanks a lot for your help.

Configure IIS for Reporting Manager and reporting server site

Dear All:

But after that, I tried to give virtual directories to reporting manager and reporting server sites. But it ain't working. Could you please help me?

My IIS 5.1
Reporting service 2005 Express with SQL Server Express
ASP.NET 2.0 Express.

Please help.

Thanks in Advance

Emmanuel MathewDear All:

But in server, even though I am getting the unformatted (Like CSS is not applied), I get the report correctly. Is there anything wrong with the ath of CSS?

And what is this "Object reference error"? Could you please help me.

Lot of thanks for the support. And thanks in Advance.

Emmanuel Mathew|||I reinstalled IIS and the server stated working fine now. I know its not the solution. But I made it working. Thanks a lot for your help.

Tuesday, March 27, 2012

Configuration File not always used

I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.

When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).

Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.

|||

I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...

What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

Configuration File not always used

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

|||

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

sql sql

Sunday, March 25, 2012

config file contents and deployement

Hi all,

I have few config files with db passwords in them. I have to change passwords daily into that(as my system is in UAT phase now). I have deployed my packages on SQL Server.

My question is , do I need to deploy my packages daily after password updation, or it'll automatically taken care by the SQL Server ?

Thanks in Advance

syed

If you are using package configuration files, you should be able to modify just the config file, and not have to re-deploy the packages.|||

thanks for ur response John !!

Can you please tell me how internally it works so that i can have a better picture ?

thanks

|||

The topic "Package Configurations" in Books Online explains it much better than I can

Basically, configurations allow you to store settings for your package outside of the pacakge (in a file, a database, env. variables, etc.). At runtime, the package reads the configurations when it first starts up, and uses the values specified in the configuration when it is executing. You can set connection strings, variable values, task properties, and more through configuration.

config file contents and deployement

Hi all,

I have few config files with db passwords in them. I have to change passwords daily into that(as my system is in UAT phase now). I have deployed my packages on SQL Server.

My question is , do I need to deploy my packages daily after password updation, or it'll automatically taken care by the SQL Server ?

Thanks in Advance

syed

If you are using package configuration files, you should be able to modify just the config file, and not have to re-deploy the packages.|||

thanks for ur response John !!

Can you please tell me how internally it works so that i can have a better picture ?

thanks

|||

The topic "Package Configurations" in Books Online explains it much better than I can

Monday, March 19, 2012

Conditional totals

I work in higher ed and have totals for student levels (undergrad, grad,
professional) and then total by institution and system. I have to total
systems that contain only community college information where there are only
undergrads. I would like to remove one of the totals based on the condition.
My example:
School Student Level #
**Community College First Time Freshmen 2100
Sophomore 1800
Total Undergrads 3900
Total at **Community College 3900
I would like to conditional remove the "Total Undergrads" for only community
colleges, but leave it for the universities within the state. Is there a way
of doing this so I don't have two totals giving the exact same total?
Thanks
--
ChrisI forgot to mention this is in a matrix format, where the sub-total give me
the situation in which I seek an answer.
Thank you much,
--
Chris
"cmcdavid" wrote:
> I work in higher ed and have totals for student levels (undergrad, grad,
> professional) and then total by institution and system. I have to total
> systems that contain only community college information where there are only
> undergrads. I would like to remove one of the totals based on the condition.
> My example:
> School Student Level #
> **Community College First Time Freshmen 2100
> Sophomore 1800
> Total Undergrads 3900
> Total at **Community College 3900
> I would like to conditional remove the "Total Undergrads" for only community
> colleges, but leave it for the universities within the state. Is there a way
> of doing this so I don't have two totals giving the exact same total?
> Thanks
> --
> Chris

Sunday, March 11, 2012

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:
I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.
-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:
I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:
I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.
As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.
There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.
-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:
Jamie Thomson wrote:

Bagles1 wrote:
I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.
As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.
There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.
-Jamie
Jamie,
Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,
Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:
Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Friday, February 17, 2012

Concurrency?

__Stephen
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> snapshot isolation, as well as database snapshots. With older versions,
> you can use dirty reads, optimistic locking and READPAST.
Do you mean, "pessimistic" locking?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Nope, optimistic. Optimistic locking allows you to release the lock right
after you read it. Then, when you go to update it, if the timestamps
(rowversions) don't match, then you can retrieve the current version. That
provides more concurrency than when you keep the row locked until the user
has finished their update.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23sN69Kn%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Do you mean, "pessimistic" locking?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:u2re6Jn%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> What I have is the statement created in my biz layer and presented back to
> the db with an "and" stipulation.
> update
> MyTable
> set myChangedColumn = NewValue , ..
> where Pkey = ThisKey
> AND myChangedColumn = OldValue , ..
> Thus I'm only changing what the user has changed, and it won't whack
> someone elses update who also changed the same value.
> Is there a better way to do this?
>
You can add a timestamp column to the table to simply the stipulation, but
basically you've got it right. It's not generally practical to use
pessimistic concurrency from clients like ASP.NET, so client-generated
optimistic concurrency is the norm.
David
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Nope, optimistic. Optimistic locking allows you to release the lock right
> after you read it. Then, when you go to update it, if the timestamps
> (rowversions) don't match, then you can retrieve the current version.
> That provides more concurrency than when you keep the row locked until the
> user has finished their update.
Right, but the important point is, SQL Server 2000's isolation levels are
all pessimistic. Applications can implement an optimistic scheme -- but
this is the application doing so, not SQL Server.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Right. I was assuming he was asking for ideas on how to minimize
concurrency problems.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O$6XNao%23FHA.3296@.TK2MSFTNGP10.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Right, but the important point is, SQL Server 2000's isolation levels are
> all pessimistic. Applications can implement an optimistic scheme -- but
> this is the application doing so, not SQL Server.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>

Concurrency?

__Stephen|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> snapshot isolation, as well as database snapshots. With older versions,
> you can use dirty reads, optimistic locking and READPAST.
Do you mean, "pessimistic" locking?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Nope, optimistic. Optimistic locking allows you to release the lock right
after you read it. Then, when you go to update it, if the timestamps
(rowversions) don't match, then you can retrieve the current version. That
provides more concurrency than when you keep the row locked until the user
has finished their update.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23sN69Kn%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Do you mean, "pessimistic" locking?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:u2re6Jn%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> What I have is the statement created in my biz layer and presented back to
> the db with an "and" stipulation.
> update
> MyTable
> set myChangedColumn = NewValue , ..
> where Pkey = ThisKey
> AND myChangedColumn = OldValue , ..
> Thus I'm only changing what the user has changed, and it won't whack
> someone elses update who also changed the same value.
> Is there a better way to do this?
>
You can add a timestamp column to the table to simply the stipulation, but
basically you've got it right. It's not generally practical to use
pessimistic concurrency from clients like ASP.NET, so client-generated
optimistic concurrency is the norm.
David|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Nope, optimistic. Optimistic locking allows you to release the lock right
> after you read it. Then, when you go to update it, if the timestamps
> (rowversions) don't match, then you can retrieve the current version.
> That provides more concurrency than when you keep the row locked until the
> user has finished their update.
Right, but the important point is, SQL Server 2000's isolation levels are
all pessimistic. Applications can implement an optimistic scheme -- but
this is the application doing so, not SQL Server.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Right. I was assuming he was asking for ideas on how to minimize
concurrency problems.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O$6XNao%23FHA.3296@.TK2MSFTNGP10.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Right, but the important point is, SQL Server 2000's isolation levels are
> all pessimistic. Applications can implement an optimistic scheme -- but
> this is the application doing so, not SQL Server.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>

Concurrency?

How do you handle this today?
I am mapping out a new system and was interested in a fresh approach.
__StephenThat's a big topic. In SQL Server 2005, concurrency has been enhanced with
snapshot isolation, as well as database snapshots. With older versions, you
can use dirty reads, optimistic locking and READPAST.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>|||Is there a specific problem you're having? Why aren't SQL Server's built-in
facilities for handling concurrency good for your situation?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Is there a specific problem you're having? Why aren't SQL Server's
> built-in facilities for handling concurrency good for your situation?
>
What I have is the statement created in my biz layer and presented back to
the db with an "and" stipulation.
update
MyTable
set myChangedColumn = NewValue , ..
where Pkey = ThisKey
AND myChangedColumn = OldValue , ..
Thus I'm only changing what the user has changed, and it won't whack someone
elses update who also changed the same value.
Is there a better way to do this?
My GUI is ASP or ASP.NET and mostly ASP today :(
__Stephen|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> snapshot isolation, as well as database snapshots. With older versions,
> you can use dirty reads, optimistic locking and READPAST.
Do you mean, "pessimistic" locking?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Nope, optimistic. Optimistic locking allows you to release the lock right
after you read it. Then, when you go to update it, if the timestamps
(rowversions) don't match, then you can retrieve the current version. That
provides more concurrency than when you keep the row locked until the user
has finished their update.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23sN69Kn%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
>> snapshot isolation, as well as database snapshots. With older versions,
>> you can use dirty reads, optimistic locking and READPAST.
> Do you mean, "pessimistic" locking?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:u2re6Jn%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
>> Is there a specific problem you're having? Why aren't SQL Server's
>> built-in facilities for handling concurrency good for your situation?
> What I have is the statement created in my biz layer and presented back to
> the db with an "and" stipulation.
> update
> MyTable
> set myChangedColumn = NewValue , ..
> where Pkey = ThisKey
> AND myChangedColumn = OldValue , ..
> Thus I'm only changing what the user has changed, and it won't whack
> someone elses update who also changed the same value.
> Is there a better way to do this?
>
You can add a timestamp column to the table to simply the stipulation, but
basically you've got it right. It's not generally practical to use
pessimistic concurrency from clients like ASP.NET, so client-generated
optimistic concurrency is the norm.
David|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Nope, optimistic. Optimistic locking allows you to release the lock right
> after you read it. Then, when you go to update it, if the timestamps
> (rowversions) don't match, then you can retrieve the current version.
> That provides more concurrency than when you keep the row locked until the
> user has finished their update.
Right, but the important point is, SQL Server 2000's isolation levels are
all pessimistic. Applications can implement an optimistic scheme -- but
this is the application doing so, not SQL Server.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Right. I was assuming he was asking for ideas on how to minimize
concurrency problems.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O$6XNao%23FHA.3296@.TK2MSFTNGP10.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
>> Nope, optimistic. Optimistic locking allows you to release the lock
>> right after you read it. Then, when you go to update it, if the
>> timestamps (rowversions) don't match, then you can retrieve the current
>> version. That provides more concurrency than when you keep the row locked
>> until the user has finished their update.
> Right, but the important point is, SQL Server 2000's isolation levels are
> all pessimistic. Applications can implement an optimistic scheme -- but
> this is the application doing so, not SQL Server.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>

Tuesday, February 14, 2012

Concurrency Help

I need a little help figuring out how to design my database/web
services for a bidding system. I'm not sure how to solve the problem
of concurrency. I'll give an analogous example of what the system is
supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
of hard drive space each, which are sold to the highest bidder in 1
gig chunks. The bidder simply tells me how much they are willing to
spend and what state or country the computer should be located in. The
system should fill their order if they have the highest bid on a gig
with their criteria. How the hell do I assign those chunks to the
highest bidder efficiently. The problem with concurrency would be when
two or more people are getting data and submitting data at the same
time. If its not done synchronously or isn't thread safe, then the
amount of available space may be incorrectly read or written, for
example.
Is there a standard way to solve this type of database/web service
design problem?David
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
SQL Server 2005 provides a new feature called SQL Service Broker
Link from BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
<Davidhere40@.gmail.com> wrote in message
news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
>I need a little help figuring out how to design my database/web
> services for a bidding system. I'm not sure how to solve the problem
> of concurrency. I'll give an analogous example of what the system is
> supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> of hard drive space each, which are sold to the highest bidder in 1
> gig chunks. The bidder simply tells me how much they are willing to
> spend and what state or country the computer should be located in. The
> system should fill their order if they have the highest bid on a gig
> with their criteria. How the hell do I assign those chunks to the
> highest bidder efficiently. The problem with concurrency would be when
> two or more people are getting data and submitting data at the same
> time. If its not done synchronously or isn't thread safe, then the
> amount of available space may be incorrectly read or written, for
> example.
> Is there a standard way to solve this type of database/web service
> design problem?
>|||On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Davidhttp://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-...
> SQL Server 2005 provides a new feature called SQL Service Broker
> Link from BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
> <Davidher...@.gmail.com> wrote in message
> news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
> >I need a little help figuring out how to design my database/web
> > services for a bidding system. I'm not sure how to solve the problem
> > of concurrency. I'll give an analogous example of what the system is
> > supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> > of hard drive space each, which are sold to the highest bidder in 1
> > gig chunks. The bidder simply tells me how much they are willing to
> > spend and what state or country the computer should be located in. The
> > system should fill their order if they have the highest bid on a gig
> > with their criteria. How the hell do I assign those chunks to the
> > highest bidder efficiently. The problem with concurrency would be when
> > two or more people are getting data and submitting data at the same
> > time. If its not done synchronously or isn't thread safe, then the
> > amount of available space may be incorrectly read or written, for
> > example.
> > Is there a standard way to solve this type of database/web service
> > design problem?
Thank you very much! I really needed that.
Dave|||On May 27, 7:27 pm, Davidher...@.gmail.com wrote:
> On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
> > Davidhttp://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-...
> > SQL Server 2005 provides a new feature called SQL Service Broker
> > Link from BOL
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
> > <Davidher...@.gmail.com> wrote in message
> >news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
> > >I need a little help figuring out how to design my database/web
> > > services for a bidding system. I'm not sure how to solve the problem
> > > of concurrency. I'll give an analogous example of what the system is
> > > supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> > > of hard drive space each, which are sold to the highest bidder in 1
> > > gig chunks. The bidder simply tells me how much they are willing to
> > > spend and what state or country the computer should be located in. The
> > > system should fill their order if they have the highest bid on a gig
> > > with their criteria. How the hell do I assign those chunks to the
> > > highest bidder efficiently. The problem with concurrency would be when
> > > two or more people are getting data and submitting data at the same
> > > time. If its not done synchronously or isn't thread safe, then the
> > > amount of available space may be incorrectly read or written, for
> > > example.
> > > Is there a standard way to solve this type of database/web service
> > > design problem?
> Thank you very much! I really needed that.
> Dave
Turns out, I think the real answer to this question was to use
timestamps. I find out on my own. SQL broker is a pain to setup and
limits you to SQL server. I don't think it's necessary in my
situation. Especially because using timestamps allows you to do as
much work simultaneously as possible and if a different timestamp is
encounted for some data, you can simply reload the data and then try
again.
Dave

Concurrency Help

Concern for open ports

when i do a netstat i get Rem Port Process CountOfRem Port
ms-sql-s System 193
it seems ms-sql-s is taking up 193 ports ? is this a problem? it seems
excessive.ms-sql-s is using one port (1433) and 192 (193-1) clients
are connected to sql server on that port. Though this kind
of behaviour is possible on a heavily used server, if one
client is using many connections then you should configure
the client application to use connection sharing/pooling.
Output of netstat command will show you who is connected
to the sql server.
192 open connections on a single port is a high number for
tcp layer and for sql server, using connection pooling
will reduce the number of active connections to this
port, and will have positive effect on application as well.
>--Original Message--
>when i do a netstat i get Rem Port Process CountOfRem
Port
> ms-sql-s System 193
>
>it seems ms-sql-s is taking up 193 ports ? is this a
problem? it seems
>excessive.
>
>.
>

Thursday, March 29, 2012

Configure IIS for Reporting Manager and reporting server site

Configure IIS for Reporting Manager and reporting server site

Tuesday, March 27, 2012

Configuration File not always used

Configuration File not always used

Sunday, March 25, 2012

config file contents and deployement

config file contents and deployement

Monday, March 19, 2012

Conditional totals

Sunday, March 11, 2012

Conditional Split Question

Friday, February 17, 2012

Concurrency?

Concurrency?

Concurrency?

Tuesday, February 14, 2012

Concurrency Help

Concurrency Help

Concurrency Help

Concern for open ports

Concatenating fields

Blog Archive

About Me