Is there a set based solution?

basically I am trying to figure out if there is a set based solution for the following situation.

When we import mainframe data to the sql server the table will look like this

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 23 0
A 2 5 23 0
A 3 13 23 0
A 4 2 23 0
A 5 12 23 0
B 1 11 14 0
B 2 7 14 0
B 3 20 14 0
B 4 6 14 0



After we run the query the table should look like ie amotize the balonhandqty and update the shortqty.

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 13 0
A 2 5 8 0
A 3 13 -5 5
A 4 2 -7 2
A 5 12 -19 12
B 1 11 3 0
B 2 7 -4 4
B 3 20 -24 20
B 4 6 -30 6

The only way I can up with a solution is by using a cursor which takes a long time. I was wondering if there is a set based or any other quicker solution.Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.|||update tbl
set BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) ,
ShortQty = case when BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) < 0 then
BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) * -1 else 0 end
from tbl t1

maybe better is

update tbl
set BalOnHandQty = a.BalOnHandQty ,
ShortQty = case when a.BalOnHandQty < 0 then a.BalOnHandQty * -1 else 0 end
from
tbl t1
join
(select PartNumber, Release, BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release)
from tbl t1) a
on a.PartNumber = t1.PartNumber
and a.Release = t1.Release|||Heck, I don't know that solving this problem is such a complicated task, but I give Kudos to Nigelrivett if he was able to figure out what the problem was based on the sparse information provided! Now THAT takes genious.

There is definitely a career for you in helpdesk support if you ever want it, Nigelrivett! ;)

blindman|||Originally posted by rdjabarov
Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.

Initially all the records for each partnumber will have the same BalOnHandQty. Then we get start subtracting demandQty from BalOnHandQty for each release for every partnumber. When the BalOnHandQty goes negative we update the shortQty.

I hope everybody can understand the problem!

I'am sorry I should have given more information when I first posted this problem.|||Heck, I don't know that solving this problem is such a complicated task
I didn't see you taking a shot at it!

nigelrivett, - that was quick! I just don't get it why the blind guy thinks you're good only for helpdesk support?|||Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.|||nigelrivett thanks for your replies. Iam not that good at set based stuff I am still trying to understand your queries. Once I get back to work I will try to run these queries on some test data and may then I can understand it better.|||rjabberon,

niggelrivett's solution is very simple and concise. That's what makes it good. "Inside every large program there is a small program screaming to get out." From what I've seen of your solutions, you'd do well to take heed.

From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.

And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman|||Originally posted by blindman
rjabberon,


Obviously is recalling a nick name more difficult than understanding the problem. Or was it sarcasm? Anyway, I bed you came up with the solution if niggelrivett (bravo!) wasn't quicker than you!|||Thanks for support, DoktorBlue! The blind guy is obviously not only blind, but also a stuck-up arrogant AH (I looked all this up following your link, bm). And I also like this phrase that I found there more and more (in fact, with every post of yours that is intended to insult me), - BLOW ME!|||Originally posted by blindman
rjabberon,

...From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.
blindman

How many of those "I.T." projects have you participated, and how many have you actually pulled through yourself, and how many of those did you manage not to bring to a complete total disaster?
Originally posted by blindman
And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman

"sarcasm" is not a "light-hearted form of humor". Check your own link...but since you're too busy fixing your "brilliant" solutions, I'll give you the right definition here:

www.dictionary.com (http://dictionary.reference.com/search?q=sarcasm)
A cutting, often ironic remark intended to wound.
A form of wit that is marked by the use of sarcastic language and is intended to make its victim the butt of contempt or ridicule.

Hypothetical (http://dictionary.reference.com/search?q=Hypothetical) - a word for the day for you.|||Originally posted by nigelrivett
Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.

Thanks a lot nigelrivett!

Your update query works great and is much quicker than my cursor based solution.

Is Replication for Me? Help in understanding...

I have a situation where I need to distribute a db to 'subscribers' for use during network, and preferred application downtime. Currently, we do this by employing MS-Access. We update our db on the 'subscriber' by sending a text file with the new data using FTP.

When the 'subscriber' opens their local copy of the Access db application, a macro fires off to check for any new file in the ftproot folder, and if one is detect that is newer than the last update, it truncates the existing table, and imports the text file using a predefined import specification format. The process works well enough as is. However, we were hoping to move beyond our dependency on MS-Access for a variety of reasons, so we are looking at developing windows forms apps using the new Asp.Net v2.0 technology and Sql2005 and SqlExpress.

I need some clarification on how replication works. Does it allow a 'snapshot' db to be created on a subscriber that can be used when the network is down? If not do we have alternatives? For example, I guess we could export/import to the subscriber in some manner.

Hope I've made the case clear enough for some responses. Thanks in advance for your thoughts and help. :)

Here's a status update...

I was able to replicate a db from my publisher/distributor Sql2005 server to a subscriber running SqlExpress. The db on the subscriber is avaiable when the network is down.

Now, the issue I have to overcome is deploying a windows form app to the subscriber to be able to read the db on a subscriber machine. I'm having trouble figuring out the connection string. I can setup the application to read from the actual name of a subscriber/instance of SqlExpress, in this example, I'm running the subscriber db on on a server named 'myClient', and the SqlExpress instance name is 'SQLEXPRESS', using integrated security.

Data Source=myClient\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

This allows the viewer app to connect to the local subscriber db, and fullfills 1 requirement, in that the viewer app can be used to view the mission critical info during network downtime or preferred application downtime.

However, since the connection string is 'hardwired' to the subscriber server name, I'd have to create as many different viewer apps as I have subscribers. That's not the way it should work. I really need to have this viewer app be able to connect to a default SqlExpress instance on whichever client subscriber server that needs it. Trouble is - I'm not sure how to set it up. I've tried using this connection string:

Data Source=localhost\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

But, I get an error message:

An error has occurred while establishing a connection to the server. ... Named Pipes Provider, error: 40 - Could not open a connection to SQL Server.

Ah, well, that is where I am at this point. End of day, so I'll look forward seeing any advice from some folks who done this type of thing, and thanks for your help.

|||

Allright! Stayed over at work to try a couple of more times to crack the issue.

I, finally, hit upon the correct connection string to use in my application:

Data Source=127.0.0.1,1433;Initial Catalog=SCIIS;Integrated Security=True

The other piece of the puzzle was in configuring SqlExpress in the Protocols. I did have TCP/IP enabled, and looking at TCP/IP properties, on the TCP/ IP Properties tab, setup the proper IP addresses with port 1433 enabled. The trick that I missed was to blank out the TCP Dynamic Ports, and changing the IPAll area to use port 1433. Once I did that and restarted SqlExpress server, I was able to connect to the local subscriber database.

As a further test, I went to the subscriber machine and disabled network connections to simulate a network downtime scenario. I started my viewer app, and successfully connected to the local db, and was able to use the viewer app w/o any issue.

Next, obstacle... How to publish the app, so I can have it be a smart client, but that's not a Sql replication issue. So, I guess, I'll say my initial question was answered.

Stay tuned...

|||

Try to enable "Shared Memory" for your SQL server protocols to see if "localhost" will work for you. You can change this setting in SQL Server Configuration Manager.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5fff42e8-377f-4b40-b0c8-b02393f8a1af.htm

Regards,

Gary

|||

Gary,

I actually had all of the protocols enabled at the time that I tested using localhost, and it didn't seem to matter. The fix for me was to change to the IP, port, and making the change to the AllPorts section as indicated above. It's working now, and we're moving forward.

Thanks for the input, though.

Is RDA a good choice for synchronization via 56k modem?

hi,

I recently got into a situation that are some of our users having problem with RDA synchronization via 56k modem. We use RDA (not replication) to connect to our web server from their WM5 handhelds. It seems that the modem connection is not very stable, sometimes may drop the connection. In this not very stable connection condition, is RDA a good choice? If I hook my PDA to my computer to sync, I never have any problem. But with RDA on modem, weird problems happen quite often. Every time when it happens, the database file gets corrupt. Any suggestion about what I should do?

Thanks.

After several month testing, it shows RDA is actually not a very reliable synchronization method over modem. Because there is a big chance modem could lost connection if it is a long synchronization. It is painful to deal with this situation, and sometimes, the user's data is just gone if the connection breaks in the middle of synchronization. We are now moving forward to CF .net 2.0 which we can transfer the database file to server with TCP/IP.

Is Physical server name stored in the Master database

Hi,
We have a situation where we want to move our current database
server to a different hardware and rename the server.
If we change the Physical server name, do we have to go thru the whole
process of changing it in sql server by running sp_dropserver,
sp_addserver?
Can we have an alias for the server name in DNS and can sql server
resolve the server name internally by going thru DNS?

For eg: Our current database server name is FFSQL-PRD01. We have it
registered in EM as FFSQL-PRD01. Now we renamed the server to
STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias of
FFSQL-PRD01( the same old name as alias). After doing this when I go
to EM and click on FFSQL-PRD01 would it give me an error? or can it
resolve the server name by going to DNS.

When you install SQl server the default instance has to be the physical
server name, Why? Is the physical server name stored in Master
database( SYSSERVERS table)?

Thanks for your help.
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1112383277.914231.158550@.o13g2000cwo.googlegr oups.com...
> Hi,
> We have a situation where we want to move our current database
> server to a different hardware and rename the server.
> If we change the Physical server name, do we have to go thru the whole
> process of changing it in sql server by running sp_dropserver,
> sp_addserver?

Yes - but this should be a quick task, and it's easily scripted if you have
many servers to change.

> Can we have an alias for the server name in DNS and can sql server
> resolve the server name internally by going thru DNS?
> For eg: Our current database server name is FFSQL-PRD01. We have it
> registered in EM as FFSQL-PRD01. Now we renamed the server to
> STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias of
> FFSQL-PRD01( the same old name as alias). After doing this when I go
> to EM and click on FFSQL-PRD01 would it give me an error? or can it
> resolve the server name by going to DNS.

As far as I know, EM will resolve the name using a SQL client alias (if you
created one in the client network utility), or the operating system's name
resolution method (DNS, in this case). But why not just change the
registration name in EM, and connect to the real name? If you have a lot of
EM registrations, then you can script them using the SQLDMO ServerGroup and
RegisteredServer objects.

> When you install SQl server the default instance has to be the physical
> server name, Why? Is the physical server name stored in Master
> database( SYSSERVERS table)?

If you have multiple instances, you need to define what 'default' means.
Since Microsoft don't know what you will call your instances, they probably
decided to go with just the physical name as the default; it also means you
can upgrade from previous MSSQL versions without having problems related to
instance naming.

> Thanks for your help.
> Geetha

To rename a server and change your EM registration is not usually a big job
(unless you have many servers to rename), so I'm not sure why you want to
use an alias to keep the old name - perhaps your real issue is how to
connect client applications to the new server name? If so, it depends on how
the client connects - you may have to change DSNs, or ADO connection
strings, or whatever. You might be able to use DNS aliases or client-side
aliases as part of a migration process, but in the longer run it will make
administration much easier if you use the real server name.

If this doesn't help, I suggest you give some more details about exactly why
you need some sort of alias, rather than just using the new server name.

Simon|||Simon Hayes wrote:
> <gdabbara@.brownshoe.com> wrote in message
> news:1112383277.914231.158550@.o13g2000cwo.googlegr oups.com...
> > Hi,
> > We have a situation where we want to move our current database
> > server to a different hardware and rename the server.
> > If we change the Physical server name, do we have to go thru the
whole
> > process of changing it in sql server by running sp_dropserver,
> > sp_addserver?
> Yes - but this should be a quick task, and it's easily scripted if
you have
> many servers to change.
> > Can we have an alias for the server name in DNS and can sql server
> > resolve the server name internally by going thru DNS?
> > For eg: Our current database server name is FFSQL-PRD01. We have
it
> > registered in EM as FFSQL-PRD01. Now we renamed the server to
> > STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias
of
> > FFSQL-PRD01( the same old name as alias). After doing this when I
go
> > to EM and click on FFSQL-PRD01 would it give me an error? or can it
> > resolve the server name by going to DNS.
> As far as I know, EM will resolve the name using a SQL client alias
(if you
> created one in the client network utility), or the operating system's
name
> resolution method (DNS, in this case). But why not just change the
> registration name in EM, and connect to the real name? If you have a
lot of
> EM registrations, then you can script them using the SQLDMO
ServerGroup and
> RegisteredServer objects.
> > When you install SQl server the default instance has to be the
physical
> > server name, Why? Is the physical server name stored in Master
> > database( SYSSERVERS table)?
> If you have multiple instances, you need to define what 'default'
means.
> Since Microsoft don't know what you will call your instances, they
probably
> decided to go with just the physical name as the default; it also
means you
> can upgrade from previous MSSQL versions without having problems
related to
> instance naming.
> > Thanks for your help.
> > Geetha
> To rename a server and change your EM registration is not usually a
big job
> (unless you have many servers to rename), so I'm not sure why you
want to
> use an alias to keep the old name - perhaps your real issue is how to

> connect client applications to the new server name? If so, it depends
on how
> the client connects - you may have to change DSNs, or ADO connection
> strings, or whatever. You might be able to use DNS aliases or
client-side
> aliases as part of a migration process, but in the longer run it will
make
> administration much easier if you use the real server name.
> If this doesn't help, I suggest you give some more details about
exactly why
> you need some sort of alias, rather than just using the new server
name.
> Simon

Hi,
Thanks a lot for your quick responses. The reason we are pushing
for DNS alias, is because we have a lot of linked server connections
and DTS package jobs that we might have to change as they are using the
server name. I just used EM as an example. I would think DTS packages
would run fine based on what you have said earlier about it being able
to resolve the name from DNS or SQl client config.

Thanks
Geetha