query equivalent: February 2012

Saturday, February 25, 2012

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanks
msnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel file
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanksmsnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel fil
e
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

Looking for some advice.....

I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.You could use a data flow to read the flat file, and use an OLEDB Destination to save it to a "temp" table. Use an Execute SQL to update the temp table, then use a second data flow to retrieve the data and send it to an OLE DB Command to call your stored procedure.

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:
> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> > I'm looking for different solution people have implemented to solve this
> > problem.
> >
> > Problem: People need to work remotely. While working remotely they want
> > to
> > gather information. This information might be survey info, or inspection
> > info, etc. This information is to be stored remotely on a handheld, or
> > laptop device. After they get back to the office/homeo office they want
> > to
> > upload the information to our corporate database, which is SQL Server
> > 2000.
> > This upload process should be very easy, like a like of a button, or done
> > automatically when the devices is docked into the corporate network. This
> > solution has to be supported over the Internet, and intranet.
> >
> > What I'm looking for is what technologies and processes have people put in
> > places to implement a solution to handle the problem I stated above.
> >
> > If you have more questions about what I am asking for please let me know.
> >
> >
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.
> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
> "Quentin Ran" wrote:
>> Replication is the MS SQL Server technology for this.
>> Quentin
>>
>> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
>> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>> > I'm looking for different solution people have implemented to solve
>> > this
>> > problem.
>> >
>> > Problem: People need to work remotely. While working remotely they
>> > want
>> > to
>> > gather information. This information might be survey info, or
>> > inspection
>> > info, etc. This information is to be stored remotely on a handheld, or
>> > laptop device. After they get back to the office/homeo office they
>> > want
>> > to
>> > upload the information to our corporate database, which is SQL Server
>> > 2000.
>> > This upload process should be very easy, like a like of a button, or
>> > done
>> > automatically when the devices is docked into the corporate network.
>> > This
>> > solution has to be supported over the Internet, and intranet.
>> >
>> > What I'm looking for is what technologies and processes have people put
>> > in
>> > places to implement a solution to handle the problem I stated above.
>> >
>> > If you have more questions about what I am asking for please let me
>> > know.
>> >
>> >
>>

Looking for Solution to Remote Data Entry

LOOKING FOR SITE WITH NOTES ON HOW I CAN CHANGE THE KNOWLEDGE OF MSSQL I HAVE TO ORAC

Hi developers!
I'm looking for the link where I can easily read and understand the way I
can transfer the knowledge I have of MSSQL to ORACLE and simply create some
procedures, views,functions,triggers,tables etc using ORACLE Database.
Anyone who can help on this will be highly appreciated.
Regards,
ComfortI'd try Oracle's site or just google it.
--Mary
On Mon, 14 Feb 2005 15:38:09 +0300, "Comfort" <comfort.peter@.stcl.com>
wrote:

>Hi developers!
>I'm looking for the link where I can easily read and understand the way I
>can transfer the knowledge I have of MSSQL to ORACLE and simply create some
>procedures, views,functions,triggers,tables etc using ORACLE Database.
>Anyone who can help on this will be highly appreciated.
>Regards,
>Comfort
>

Looking for simple sample

Hello

I'm looking around for tutorials and samples for Notification Services applications for a while now, but I just can't find something that covers what I want to do. All I want is to check if a value in my Database changes or exceeds a specific value and then send a mail.

Does anyone know a good sample that show's me how to do this or can anyone explain it in a couple of words?

Thanks in advance!

This is a fairly common scenario. Use the SQL Server Event Provider to query your database table, looking for new or changed records. In your Subscription Class, have each subscriber specify the threshold they are interested in and in the match rule compare the two to produce the notifications.

The Stock sample application that comes with SSNS would be an excellent place to start. Just replace the FileSystemWatcher Event Provider with the SQL Server Event Provider and you're most of the way there.

You'll probably want to use at least one Chronicles table to prevent unwanted/uninteresting notifications. For example, if the threshold is continually exceeded, you probably don't want to continually email your subscribers. To use the Stock sample to illustrate: if I want to know when Microsoft's stock exceeds $45/share. No big deal. But if on 5 consecutive generator firings the stock values are $44.90, $45.10, $45.20, $45.50, and $46.00, without careful planning you will have just generated 4 emails to me. To prevent the unwanted notifications, use the chronicles table - either keep track of the highwater mark, or the fact that you've already sent out a notification, or whatever makes the most sense in your situation. BTW - if you have a copy of my book, I discuss this in Chapter 7, but I'll be glad to continue the conversation here, too.

HTH...

Joe

Looking for server and db monitoring scripts

Hello ladies and gentlemen.

I am scouring the net to find SQL2k admin scripts. I am interested in both monitoring of the server as well as the rdbms.

I'm sure this question has been presented many times, but I did not find referenct to it. I'm sorry if this is a very old subject.

I am change rdbms from oracle to sql2k and am having to put in extra effort to change my mind set. Though similar, there are many, many differences.

Any help would be very appreciated.

I will be getting my ms dba certifications soon, and look forward to helping with your dbforums communnity.

Thank youI reccomend a couple good books for monitoring and performance type thigns, I would not reccomend using a bunch of scripts until you know what they are doing.

Two books - Inside SQL Server 2000 by Kalen Delaney and Performance Monitoring with SQL Server 2000 by MSPress.

HTH|||I've been looking for good SQL2k books as well. Thank you for your help.
<br><Br>

Originally posted by rhigdon
I reccomend a couple good books for monitoring and performance type thigns, I would not reccomend using a bunch of scripts until you know what they are doing.

Two books - Inside SQL Server 2000 by Kalen Delaney and Performance Monitoring with SQL Server 2000 by MSPress.

HTH

looking for security guidelines to avoid sql injection risks with xquery

Hi, i hope this forum can help me clarify my security concerns for my scenario:

I want a user to be able to filter sql rows by using xquery on an untyped XML that is stored in a XMLDataType column. My table will have multiple rows/columns, with one column being XML.

I would like to get some guidance on how a user can specify their "filter" condition string using the xquery syntax without harding coding any dependency on the server side (client decides which items they are interested in), and I would like to know the guidelines on how to translate the xquery on the server to avoid any sql injection risks. For instance, perhaps i should double all single quotes in the given xquery string. Also, i am concerned about sql:variables that may be able to access private variables inside a stored proc, has this been an issue?

Thanks!!

All of the issues you bring up are concerns:

-SQL injection
-sql:column
-sql:variable
-very expensive queries can lead to Denial of Service attacks

XQuery is code, so it should be treated similarly to SELECT statements, in general it is difficult to safely execute untrusted queries.

Ways around this would be to:

-Use set of parameterized stored procedures that reasonably cover the likely set of queries users are going to execute.
-Give each user their own database and assume that they have full SELECT permissions on it. (This wont prevent Denial of Service type attacks, but could prevent users' from access other users' data in the event of SQL injection.)
-Use full-text search. The queries wont be as expressive, but it might be good enough to filter rows.

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than 24 hoursLook at Tracy's suggestion at:
http://www.realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html#extended
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:OaYXTC9%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran Abdurrahman wrote:
> looking for script to backup Transaction log and delete any thing older than
> 24 hours
>
Start here:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup\'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> > looking for script to backup Transaction log and delete any thing older than
> > 24 hours
> >
> >
> Start here:
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> Start here:
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Looking for sample code for doing store procedures

I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.

I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.

If you know of any good article or samples, please let me know.

TIA,

Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||

Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.

TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm

Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

Looking for references for querying Active Directory (AD) through SQL Server

Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.

Thanks.

There are a lot of examples out there:

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410

http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Looking for recommended approach to merging records

I am trying to create a dimension table and I am pulling in data from two tables to create it. I need all records from table A, any records from table B that are not in table A, and I need to use the fields from B for those records that do match. What would be the best way to approach this, merge join + derived columns, union all + aggrigation? Any suggestions?

It seems like it's harder to do this in ssis rather then just doing it in the database.

If you think it is easierr to do it in the database then use the database. It is an oft made assumption that if you're using SSIS then you should use it to do EVERYTHING. That's not the case - database engines do what they do fantastically well so if you can use it either by:

Writing SQL in an OLE DB Source

Looking for real-world situation examples or tutorials on replication

Marcel,
You might want to look at:
http://www.microsoft.com/technet/pro.../sql/2000/main
tain/mergperf.mspx
http://www.microsoft.com/technet/pro.../sql/2000/main
tain/tranrepl.mspx
Hilary Cotter has a book that is coming out sometime this
month (http://www.nwsu.com/0974973602p.html)
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Hi Paul,
Thanks for the info, that book looks very promising.
regards,
Marcel
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schreef in bericht
news:1b0d01c4c269$d7197160$a501280a@.phx.gbl...
> Marcel,
> You might want to look at:
> http://www.microsoft.com/technet/pro.../sql/2000/main
> tain/mergperf.mspx
> http://www.microsoft.com/technet/pro.../sql/2000/main
> tain/tranrepl.mspx
> Hilary Cotter has a book that is coming out sometime this
> month (http://www.nwsu.com/0974973602p.html)
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>

Looking for RDBM reviews and ratings

I am looking for reviews and rating of the top RDBM products. I would like
to know how SQL compares to other products.
Rich
Rich,
in terms of performance and cost, the Transaction Processing Performance
Council maintain a set of independant comparisons you may find useful:
http://www.tpc.org/.
Regards,
Paul Ibison
|||This is always a tough question... I would google it... and I would also
check the web sites of each of the vendors, each vendors site will usually
have a comparison and show the weak points of the competitor..
The TPC web site will show some comparative performance information, but
don't choose based on a single test... Each vendor ups the other, and the
top spots change from one vendor to the next, like a ping pong game... Look
for trends - ie for small data warehouses does one vendor seem to lead
everywhere... What about for extremely large warehouses, etc... you can
also sometimes get a feel for what hardware works well for a particular DBMS
from the TPC numbers as well..
Then do your own testing... compare the prices, support policies, ease of
use and maintenance, availability of skilled people to program using the db,
etc and pick what you feel is best...
Now days, almost all of the products are so good you can be successfull with
any of them, so just pick the best fit..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>
|||Hi Richard.
I like going to the various vendors sites for comparisons as they all love
to tell you what their product does that the others don't (:
Other than TPC, you might also consider visiting a few ERP vendors such as
sap, peoplesoft, jde etc as they sometimes have comparative benchmarks. ERPs
owned by software vendors such as Oracle 11i, Greatplains etc are obvious
exceptions..
Regards,
Greg Linwood
SQL Server MVP
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>
|||The problem that I have is dealing with properity vendors for the front end
bussiness software which have their own database solutions like 'Progress'
and others but I can find very little DB information for their products.
Corporate management is going to make product decision but my concern is for
some open architecture in the DB services.
Rich
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZcjZjaJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi Richard.
> I like going to the various vendors sites for comparisons as they all love
> to tell you what their product does that the others don't (:
> Other than TPC, you might also consider visiting a few ERP vendors such as
> sap, peoplesoft, jde etc as they sometimes have comparative benchmarks.
ERPs[vbcol=seagreen]
> owned by software vendors such as Oracle 11i, Greatplains etc are obvious
> exceptions..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
like
>
|||Hi Richard,
I am including the following articles for your reference.
SQL Server 2000 for Oracle Database Professionals
http://www.microsoft.com/sql/evaluat...are/oracle.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. Oracle developers and
IT professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for IBM Database Professionals
http://www.microsoft.com/sql/evaluat...bm/default.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. IBM developers and IT
professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for Sybase Database Professionals
http://www.microsoft.com/sql/evaluat...are/sybase.asp
Discover how to reduce costs, improve performance, and improve business
agility by migrating from Sybase to SQL Server.
Companies Switching to SQL Server
http://www.microsoft.com/sql/evaluat.../switchers.asp
Companies are recognizing the rich capabilities of SQL Server, including
its ability to get them to market faster at significantly reduced
operational costs. Find out why these customers made the switch to SQL
Server.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Looking for RDBM reviews and ratings

I am looking for reviews and rating of the top RDBM products. I would like
to know how SQL compares to other products.
RichRich,
in terms of performance and cost, the Transaction Processing Performance
Council maintain a set of independant comparisons you may find useful:
http://www.tpc.org/.
Regards,
Paul Ibison|||This is always a tough question... I would google it... and I would also
check the web sites of each of the vendors, each vendors site will usually
have a comparison and show the weak points of the competitor..
The TPC web site will show some comparative performance information, but
don't choose based on a single test... Each vendor ups the other, and the
top spots change from one vendor to the next, like a ping pong game... Look
for trends - ie for small data warehouses does one vendor seem to lead
everywhere... What about for extremely large warehouses, etc... you can
also sometimes get a feel for what hardware works well for a particular DBMS
from the TPC numbers as well..
Then do your own testing... compare the prices, support policies, ease of
use and maintenance, availability of skilled people to program using the db,
etc and pick what you feel is best...
Now days, almost all of the products are so good you can be successfull with
any of them, so just pick the best fit..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>|||Hi Richard.
I like going to the various vendors sites for comparisons as they all love
to tell you what their product does that the others don't (:
Other than TPC, you might also consider visiting a few ERP vendors such as
sap, peoplesoft, jde etc as they sometimes have comparative benchmarks. ERPs
owned by software vendors such as Oracle 11i, Greatplains etc are obvious
exceptions..
Regards,
Greg Linwood
SQL Server MVP
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>|||The problem that I have is dealing with properity vendors for the front end
bussiness software which have their own database solutions like 'Progress'
and others but I can find very little DB information for their products.
Corporate management is going to make product decision but my concern is for
some open architecture in the DB services.
Rich
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZcjZjaJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi Richard.
> I like going to the various vendors sites for comparisons as they all love
> to tell you what their product does that the others don't (:
> Other than TPC, you might also consider visiting a few ERP vendors such as
> sap, peoplesoft, jde etc as they sometimes have comparative benchmarks.
ERPs
> owned by software vendors such as Oracle 11i, Greatplains etc are obvious
> exceptions..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
like[vbcol=seagreen]
>|||Hi Richard,
I am including the following articles for your reference.
SQL Server 2000 for Oracle Database Professionals
http://www.microsoft.com/sql/evalua...pare/oracle.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. Oracle developers and
IT professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for IBM Database Professionals
http://www.microsoft.com/sql/evalua...ibm/default.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. IBM developers and IT
professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for Sybase Database Professionals
http://www.microsoft.com/sql/evalua...pare/sybase.asp
Discover how to reduce costs, improve performance, and improve business
agility by migrating from Sybase to SQL Server.
Companies Switching to SQL Server
http://www.microsoft.com/sql/evalua...e/switchers.asp
Companies are recognizing the rich capabilities of SQL Server, including
its ability to get them to market faster at significantly reduced
operational costs. Find out why these customers made the switch to SQL
Server.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Looking for RDBM reviews and ratings

I am looking for reviews and rating of the top RDBM products. I would like
to know how SQL compares to other products.
RichRich,
in terms of performance and cost, the Transaction Processing Performance
Council maintain a set of independant comparisons you may find useful:
http://www.tpc.org/.
Regards,
Paul Ibison|||This is always a tough question... I would google it... and I would also
check the web sites of each of the vendors, each vendors site will usually
have a comparison and show the weak points of the competitor..
The TPC web site will show some comparative performance information, but
don't choose based on a single test... Each vendor ups the other, and the
top spots change from one vendor to the next, like a ping pong game... Look
for trends - ie for small data warehouses does one vendor seem to lead
everywhere... What about for extremely large warehouses, etc... you can
also sometimes get a feel for what hardware works well for a particular DBMS
from the TPC numbers as well..
Then do your own testing... compare the prices, support policies, ease of
use and maintenance, availability of skilled people to program using the db,
etc and pick what you feel is best...
Now days, almost all of the products are so good you can be successfull with
any of them, so just pick the best fit..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>|||Hi Richard.
I like going to the various vendors sites for comparisons as they all love
to tell you what their product does that the others don't (:
Other than TPC, you might also consider visiting a few ERP vendors such as
sap, peoplesoft, jde etc as they sometimes have comparative benchmarks. ERPs
owned by software vendors such as Oracle 11i, Greatplains etc are obvious
exceptions..
Regards,
Greg Linwood
SQL Server MVP
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am looking for reviews and rating of the top RDBM products. I would like
> to know how SQL compares to other products.
> Rich
>|||The problem that I have is dealing with properity vendors for the front end
bussiness software which have their own database solutions like 'Progress'
and others but I can find very little DB information for their products.
Corporate management is going to make product decision but my concern is for
some open architecture in the DB services.
Rich
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZcjZjaJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi Richard.
> I like going to the various vendors sites for comparisons as they all love
> to tell you what their product does that the others don't (:
> Other than TPC, you might also consider visiting a few ERP vendors such as
> sap, peoplesoft, jde etc as they sometimes have comparative benchmarks.
ERPs
> owned by software vendors such as Oracle 11i, Greatplains etc are obvious
> exceptions..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ejvkYpMJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > I am looking for reviews and rating of the top RDBM products. I would
like
> > to know how SQL compares to other products.
> >
> > Rich
> >
> >
>|||Hi Richard,
I am including the following articles for your reference.
SQL Server 2000 for Oracle Database Professionals
http://www.microsoft.com/sql/evaluation/compare/oracle.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. Oracle developers and
IT professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for IBM Database Professionals
http://www.microsoft.com/sql/evaluation/compare/ibm/default.asp
SQL Server 2000 provides organizations with a scalable, reliable, and
affordable relational database and analysis solution. IBM developers and IT
professionals can learn more about the benefits of using SQL Server by
reviewing these resources.
SQL Server 2000 for Sybase Database Professionals
http://www.microsoft.com/sql/evaluation/compare/sybase.asp
Discover how to reduce costs, improve performance, and improve business
agility by migrating from Sybase to SQL Server.
Companies Switching to SQL Server
http://www.microsoft.com/sql/evaluation/compare/switchers.asp
Companies are recognizing the rich capabilities of SQL Server, including
its ability to get them to market faster at significantly reduced
operational costs. Find out why these customers made the switch to SQL
Server.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Looking for query plan determination information

Hi,

I was just helping a coworker optimize a query. He had two versions:
one which used UNION for each value for which he was tallying results
and another query which used GROUP BY. Here is an aproximation of what
they were:

Query #1:
---
SELECT 12 AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
AND T.col2 = 5
UNION
SELECT 13 AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
AND T.col2 = 6

Query #2:
---
SELECT R.row_num AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
INNER JOIN Report_Rows R ON R.col2 = T.col2
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
GROUP BY ALL R.row_num
ORDER BY R.row_num

The Report_Rows table in this case would have had two rows mapping row
12 to a column value of 5 and row 13 to a column value of 6. The
second query was performing horribly until I noticed the ALL keyword
in the GROUP BY, which I didn't think was necessary. When I removed
that it performed more like I expected it to perform.

Before I had noticed that I was scouring over the query plans and
couldn't figure out why in one instance the query optimizer chose to
join My_Table and Another_Table, yet when the ALL keyword was there it
chose to return all of the records from Another_Table (a rather large
table) and join it to the Report_Rows table before then joining to
My_Table, which had the date criteria in the WHERE clause.

So, if you've read this far without giving up...

1. Why would the ALL keyword cause this? I understand the
functionality of ALL, but I still don't see why that caused the
reordering of the joins.

2. (more importantly) Are there any good resources that you know of
that explain how the query optimizer choices its query paths? Do the
"Inside SQL Server" books go into that much detail? Any good online
resources?

Thanks!
-Tom."Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:a2c0eeb8.0309160626.901177@.posting.google.com ...
> Hi,
> I was just helping a coworker optimize a query. He had two versions:
> one which used UNION for each value for which he was tallying results
> and another query which used GROUP BY. Here is an aproximation of what
> they were:
> Query #1:
> ---
> SELECT 12 AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> AND T.col2 = 5
> UNION
> SELECT 13 AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> AND T.col2 = 6
> Query #2:
> ---
> SELECT R.row_num AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> INNER JOIN Report_Rows R ON R.col2 = T.col2
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> GROUP BY ALL R.row_num
> ORDER BY R.row_num
> The Report_Rows table in this case would have had two rows mapping row
> 12 to a column value of 5 and row 13 to a column value of 6. The
> second query was performing horribly until I noticed the ALL keyword
> in the GROUP BY, which I didn't think was necessary. When I removed
> that it performed more like I expected it to perform.
> Before I had noticed that I was scouring over the query plans and
> couldn't figure out why in one instance the query optimizer chose to
> join My_Table and Another_Table, yet when the ALL keyword was there it
> chose to return all of the records from Another_Table (a rather large
> table) and join it to the Report_Rows table before then joining to
> My_Table, which had the date criteria in the WHERE clause.
> So, if you've read this far without giving up...
> 1. Why would the ALL keyword cause this? I understand the
> functionality of ALL, but I still don't see why that caused the
> reordering of the joins.
> 2. (more importantly) Are there any good resources that you know of
> that explain how the query optimizer choices its query paths? Do the
> "Inside SQL Server" books go into that much detail? Any good online
> resources?
> Thanks!
> -Tom.

It's almost impossible (at least for me) to know why the optimizer chose a
particular plan without knowing the table structures, indexes and amount of
data, and even with that knowledge, it may not be clear at all. So I can't
say much about your first question, but I can definitely recommend Inside
SQL Server 2000 for a great explanation of what the optimizer considers when
it produces a query plan. There's a lot of detail, including how to go about
using query plans to tune individual queries. Another useful book is
Advanced Transact SQL for SQL Server 2000, which also explains many of the
examples with reference to their query plans.

Simon|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> Before I had noticed that I was scouring over the query plans and
> couldn't figure out why in one instance the query optimizer chose to
> join My_Table and Another_Table, yet when the ALL keyword was there it
> chose to return all of the records from Another_Table (a rather large
> table) and join it to the Report_Rows table before then joining to
> My_Table, which had the date criteria in the WHERE clause.

I can only echo Simon's reply that without table definitions etc, this
is difficult to tell. In fact, even with all information available,
this might be difficult to tell. Understanding the output of a cost-
based optimizer is by no means an easy task.

> 2. (more importantly) Are there any good resources that you know of
> that explain how the query optimizer choices its query paths? Do the
> "Inside SQL Server" books go into that much detail? Any good online
> resources?

Certainly, you learn a great deal from Kalen's book. But I also like to
add that that experience counts a lot too. And some creative thinking.
The basic thing to understand is why a table scan may be better than
an index seek. This is something which also can be extended to joins.
That is a scan + merge/hash join may be faser than seek + loop join.

But then there are all such wild things which includes parallelism that
I find myself understanding only fragments of.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you both for the input. I had tried to duplicate the effect with
test tables, but as you know, the query optimizer takes a lot into
account and I couldn't find an example that would be practical for
posting here.

I will give the two books that Simon suggested a more thorough read.
I've seen adverts for Kalen's online webinars as well, so perhaps I'll
look into those.

> But then there are all such wild things which includes parallelism that
> I find myself understanding only fragments of.
I hate to think of something that is complex enough that you have
trouble understanding it Erland... ;-)

Thanks again!
-Tom.

Looking for query help

Can anyone see why this only works if an address had no enddate? Something is going wrong in the WHERE Clause, if the enddate is null it works fine. What I need is to look at the enddate and if it is Null or >= today the record should be returned. I only want to use the Month and Day of the date.

Thank you for any help,


CREATE PROCEDURE [dbo].[sp_DataReaderName] @.SID int AS
SELECT
 CASE
 WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName]
 ELSE A.[CompanyName]
 END AS DRName,
 C.Client_ID
FROM
 tblClients C
INNER JOIN
 tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE
 (C.Client_ID = @.SID) AND
 (A.MailTo=1) AND
 (A.EndDate Is Null OR (DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())))
GO

Its late and I've not spotted anything obvious except...DON'T prefix with "sp_" you'll incur a needless performance cost. SQL Server will think its a Microsoft Proc and go straight to Master.

Looking for query equivalent

To extract a certain type of information we use a query like this in Oracle:

WHERE MOD(TRUNC(ACCOUNT_TYPE / POWER(2,0)),1) > 0

I am hoping someone can help me with the equivalent query syntax for sql server

Scott

Does POWER(2,0) mean "two to the zero power"? If so, why not just write 1? It appears that POWER(2,x) mean the same in both dialects.

Does MOD(x,1) mean MODULUS? Are you wanting the fractional part of x? If so try x - floor(x)

I am guessing that floor(x) is the same as TRUNC(x)

The "Modulus" operator in SQL server is the "%" operator; for instance 17%3 = 2; 21%10=1

Give a look to "Mathematical Functions" in books online

(Somebody check me, please)

|||

Do would it look something like this:

MOD(m,n) - where mod returns the remainder of m divided by n

TRUNC(num, x) - A number is truncated to x decimal places

Power(x,y) - x raised to the y power

Oracle Version: MOD(TRUNC(ACCOUNT_TYPE / POWER(2, $x)), 2) > 0

SQL Version: floor(ACCOUNT_TYPE / POWER(2, $x))% 2) > 0

Maybe?

|||

If your looking to deterine the xth bit, probably:

set nocount on
declare @.mockUp table
( ACCOUNT_TYPE integer,
powerOfTwo tinyint
)
insert into @.mockUp values (29, 3)
insert into @.mockUp values (14, 0)
insert into @.mockUp values (3, 2)
insert into @.mockup values (900, 7)
insert into @.mockup values (625, 5)
insert into @.mockup values (convert(int, 0xffffffff), 1)
insert into @.mockup values (convert(int, 0xffffffff), 2)
insert into @.mockup values (convert(int, 0xffffffff), 3)
insert into @.mockup values (convert(int, 0xffffffff), 4)
insert into @.mockup values (convert(int, 0xffffffff), 5)
insert into @.mockup values (convert(int, 0xffffffff), 6)
insert into @.mockup values (convert(int, 0xffffffff), 7)
insert into @.mockup values (convert(int, 0xffffffff), 8)
insert into @.mockup values (convert(int, 0xffffffff), 9)
insert into @.mockup values (convert(int, 0xffffffff), 10)
insert into @.mockup values (convert(int, 0xffffffff), 11)
insert into @.mockup values (convert(int, 0xffffffff), 12)
insert into @.mockup values (convert(int, 0xffffffff), 13)
insert into @.mockup values (convert(int, 0xffffffff), 14)
insert into @.mockup values (convert(int, 0xffffffff), 15)
insert into @.mockup values (convert(int, 0xffffffff), 16)
insert into @.mockup values (convert(int, 0xffffffff), 17)
insert into @.mockup values (convert(int, 0xffffffff), 18)
insert into @.mockup values (convert(int, 0xffffffff), 19)
insert into @.mockup values (convert(int, 0xffffffff), 20)
insert into @.mockup values (convert(int, 0xffffffff), 21)
insert into @.mockup values (convert(int, 0xffffffff), 22)
insert into @.mockup values (convert(int, 0xffffffff), 23)
insert into @.mockup values (convert(int, 0xffffffff), 24)
insert into @.mockup values (convert(int, 0xffffffff), 25)
insert into @.mockup values (convert(int, 0xffffffff), 26)
insert into @.mockup values (convert(int, 0xffffffff), 27)
insert into @.mockup values (convert(int, 0xffffffff), 28)
insert into @.mockup values (convert(int, 0xffffffff), 29)
insert into @.mockup values (convert(int, 0xffffffff), 30)
select ACCOUNT_TYPE,
powerOfTwo,
floor(ACCOUNT_TYPE / POWER(2, powerOfTwo))% 2 as [floor routine],
(ACCOUNT_TYPE & POWER(2, powerOfTwo))/power(2, powerOfTwo) as [Masked routine]
from @.mockUp
-- ACCOUNT_TYPE powerOfTwo floor routine Masked routine
-- - - --
-- 29 3 1 1
-- 14 0 0 0
-- 3 2 0 0
-- 900 7 1 1
-- 625 5 1 1
-- -1 1 0 1
-- -1 2 0 1
-- ...
-- -1 29 0 1
-- -1 30 0 1

Looking for Query

SQL SERVER 2000
I have to tables
Students:
StudentSysID (INT) PK
StudentName (VCHAR 100)
NumTeachers (INT)
Classes:
SysID (INT) PK
StudentSysID (INT)
TeacherID (INT)
I am looking for a query to do the following
UPDATE Students SET NumTeachers = (SELECT DISTINCT COUNT(TeacherID) FROM
Classes WHERE Students.StudentSysID = Classes.StudentSysID)
Which would SEEM give me the number of DIFFERENT teachers each student has,
but its not, it gives me the total number of class records for each student.
What am i doing wrong?
TIA
Tim MorrisonDISTINCT should be inside the COUNT. Try if this works.
UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)|||Without table structures & sample data for a repro script is hard to
identify the problem. Pl. refer to www.aspfaq.com/5006 to provide required
information for others to better identify the issue.
Anith|||I thought it would be something simple....
Much appreciated.
Tim Morrison
"Green" <subhash.daga@.gmail.com> wrote in message
news:1137098333.822885.223540@.f14g2000cwb.googlegroups.com...
> DISTINCT should be inside the COUNT. Try if this works.
> UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
> FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)
>

Looking for process Ideas

I'm looking for Ideas on how to handle a Pldege Reminder process. For example; a pledge is made to pay $2400 over the next two years. They will pay $100 per month and each month a reminder will be sent. No real mistery if there is a balance you send a reminder. My problem is how to handle things like what if they want to pay quarterly or annually and how to determine if a payment is really due based on when they paid last, etc... You most likely see what I mean.

If anyone has done this in the past and/or has any ideas and is willing to share I would greatly appreciate any help.

Some stuff that may help you help me better:

tblClient (ClientID)

tblPledge (PledgeID, ClientID, PledegedAmt, PledgeDate,Frequency,NumberYears)

tblPledgePayments (PmtID, PledgeID,PmtAmt,PmtDate)

Using the following definition:

Client (Id, Name, Email)

Pledge (Id, ClientId, PledgedAmt, PledgeDate, Frequency, NumberOfPeriods)

PledgePayments (Id, PledgeId,PmtAmt,PmtDate)

Try

SELECT Name, Email FROM Client WHERE Id IN (

SELECT ClientId FROM Pledge, PledgePayments WHERE Frequency='M' AND PledgePayments.PledgeId = Pledge.Id

AND (SUM(PmtAmt) < (PledgedAmt * (DateDiff(m, GetDate(), PledgeDate) / NumberOfPeriods)))

You will need to look up DateDiff in Books-on-line and check that m is the indicator for months. Also that GetDate() and PledgeDate are the right way around.

HTH

|||Have you done this sort of thing in the past with pledges?|||

No! It is however a fairly simple bit of SQL. You will need to test the query with a number of test cases.

Looking for physical design suggestions...

I got a server that has a RAID-5 array partitioned into C: and D:
drives (OS Win2K Adv. Server installed on C:). The server also has a
mapping to a NAS device using the latest protocols that trick the
system into thinking the map is actually a local SCSII drive. That's
drive X:.
This server is used only for SQL, and contains an OLTP database that
sees a lot of use and is pretty heavily indexed.
I am toying with the idea of centralizing my data storage on the NAS
(data center network segment is 1-gigabit ethernet). So I was
thinking about putting my primary data file on the NAS (drive X:) and
keeping all tables there, creating a secondary data file on local
RAID-5 (drive D:) and putting all non-clustered indexes there, as well
as keeping the tempdb there and specifying the sort in tempdb option.
Log files would also remain on D:.

If anyone can suggest a better scenario given the above setup - I'd
love to hear it. Much appreciated.

Alexey AksyonenkoAlthough it is technically possible to put SQLServer database files on a NAS
drive, this is not supported by Microsoft and they strongly recommended that
you don't do it. With a NAS drive you lose many of the benefits of a
client-server database, you will get poor performance and you risk
corrupting your data.

Databases belong on direct-attached or SAN storage.

--
David Portas
----
Please reply only to the newsgroup
--

"Alexey Aksyonenko" <Alexey.Aksyonenko@.coanetwork.com> wrote in message
news:1449e414.0309260612.7558f05f@.posting.google.c om...
> I got a server that has a RAID-5 array partitioned into C: and D:
> drives (OS Win2K Adv. Server installed on C:). The server also has a
> mapping to a NAS device using the latest protocols that trick the
> system into thinking the map is actually a local SCSII drive. That's
> drive X:.
> This server is used only for SQL, and contains an OLTP database that
> sees a lot of use and is pretty heavily indexed.
> I am toying with the idea of centralizing my data storage on the NAS
> (data center network segment is 1-gigabit ethernet). So I was
> thinking about putting my primary data file on the NAS (drive X:) and
> keeping all tables there, creating a secondary data file on local
> RAID-5 (drive D:) and putting all non-clustered indexes there, as well
> as keeping the tempdb there and specifying the sort in tempdb option.
> Log files would also remain on D:.
> If anyone can suggest a better scenario given the above setup - I'd
> love to hear it. Much appreciated.
> Alexey Aksyonenko|||Thanks David. Actually, they came out with this new protocol that as I
said makes the system think that the mapping is a local drive. But I
will definitely keep this in mind. Any suggestions on the actual
physical design portion, NAS aside?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Alexey Aksyonenko" <alexey.aksyonenko@.coanetwork.com> wrote in message
news:3f7469fb$0$62077$75868355@.news.frii.net...
> Thanks David. Actually, they came out with this new protocol that as I
> said makes the system think that the mapping is a local drive. But I
> will definitely keep this in mind. Any suggestions on the actual
> physical design portion, NAS aside?

Ignoring the NAS the answer is: Doesn't really matter. Since your C and D
drives are logical partitions of a single physical RAID 5 disk, it won't
make much of a difference how you lay out the files.

>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Looking for options to fix a poorly implemented solution

So, I've started a new job recently where I am doing work on a SQL
Server database designed by a third party software company, which also
wrote the client software accompanying the database. The client
software is crap, the data model sucks rocks, and I'm stuck with it.
So in no way can I modify client code, or redesign any element of
relational model. I've been able to modify stored procedures and
triggers for performance, perform regular maintenance tasks, and
upgrade all upgradable elements, but that's about it. On top of that,
people developed an internal method of using the datbase that is
causing it to grow way too fast. Basically, there is one table with a
BLOB field that people are using to store Word documents containing
scanned images (part of their efforts to go to a paperless system).
Documents are scanned only when there is no other way to get the
information into the system (client signatures, legal documents from
courts, etc). And I've been charged with getting the size of the
database down. Yay.
Brainstorming for solutions, I was wondering if there is any possible
way to compress BLOB fields in a way that is completely transparent to
the client. I can't think of a way, so I have little hope for that
idea. Another is to go in and make sure all of the images are
compressed. That will only yield marginal results. Finally, going in
and replacing all of the scanned documents with documents that point
to a UNC path with all of the extracted documents is a solution that
will required quite a bit of work, but is possible. (If it were up to
me, I'd just say to heck with it and keep some documents in a paper
system). Any thought about what I could do?
Thanks!When I was at Sprint, the Place Where Consultants Go To Be Punished, our
department got into a document archiving frenzy. Ended up storing all
documents into PDFs, storing them on the server, with only a link to the
document in the database.
- Wm
"AAAWalrus" <aaawalrus@.yahoo.com> wrote in message
news:8b266bc2.0312101204.32a99d74@.posting.google.com...
> So, I've started a new job recently where I am doing work on a SQL
> Server database designed by a third party software company, which also
> wrote the client software accompanying the database. The client
> software is crap, the data model sucks rocks, and I'm stuck with it.
> So in no way can I modify client code, or redesign any element of
> relational model. I've been able to modify stored procedures and
> triggers for performance, perform regular maintenance tasks, and
> upgrade all upgradable elements, but that's about it. On top of that,
> people developed an internal method of using the datbase that is
> causing it to grow way too fast. Basically, there is one table with a
> BLOB field that people are using to store Word documents containing
> scanned images (part of their efforts to go to a paperless system).
> Documents are scanned only when there is no other way to get the
> information into the system (client signatures, legal documents from
> courts, etc). And I've been charged with getting the size of the
> database down. Yay.
> Brainstorming for solutions, I was wondering if there is any possible
> way to compress BLOB fields in a way that is completely transparent to
> the client. I can't think of a way, so I have little hope for that
> idea. Another is to go in and make sure all of the images are
> compressed. That will only yield marginal results. Finally, going in
> and replacing all of the scanned documents with documents that point
> to a UNC path with all of the extracted documents is a solution that
> will required quite a bit of work, but is possible. (If it were up to
> me, I'd just say to heck with it and keep some documents in a paper
> system). Any thought about what I could do?
> Thanks!

Looking for opinions.....want to use SQL Server to store images

I have a client who wants to be able to upload images to his website for his customers to access. It will probably max out at 100 images a month...so not a huge amount of data. I am using asp.net 2.0 and SQL Server 2005.

Does anyone have thoughts or opinions on why I should or should not take this approach?

You could store images on disk or you could store images in SQL server.
It would seem that SQL would require more overhead than Disk so...

Does SQL offer you something that you require that disk storage does not?

If it does, then use it. Pulling images from a db is not an uncommon practice. The small performance drop could be mitigated by caching the images (in-memory for small images or disk for large images).

If nothing you are doing actually requires SQL server though, i would stick to disk.

|||I would use SQL Server. It simplifies quite a bit, and you don't have to give users write permissions any where on the web server making it more secure.|||

I am already using sql server for other reasons so I wouldn't be setting it up specifically for this. Also I would think that sql server would organize the files for you, where they live and whatnot, thus less that I would have to deal with from a programmatic standpoint, not to mention the directory permissions as mentioned above. The images that will be stored will be a little larger than a business card.

Thanks for your responses..

looking for non similar orders

System is "MFG" Custom jobs.
Tracking customer's last order and looking for changes they make in the job
detail table as compared to order before.
Table schema:
OrderID, line#, Item, QtyReq
I need to verify the last order and if it changed with the clients prior
order.
I have 6 line items per order, and the rows 1,2,3 are the critical check
point I need to verify.
If it returned int for # of rows diff that would be fine.
http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 USD
per month.Hi
Check out http://www.aspfaq.com/etiquett_e.asp?id=5006 on how to post DDL
and example data. It is also useful to post your current attempt and the
information you expect from the example data.
John
"Stephen Russell" wrote:

> System is "MFG" Custom jobs.
> Tracking customer's last order and looking for changes they make in the jo
b
> detail table as compared to order before.
> Table schema:
> OrderID, line#, Item, QtyReq
> I need to verify the last order and if it changed with the clients prior
> order.
> I have 6 line items per order, and the rows 1,2,3 are the critical check
> point I need to verify.
> If it returned int for # of rows diff that would be fine.
> http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 US
D
> per month.
>
>
>
>

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

Saturday, February 25, 2012

Looking for some general feedback on working with SQL, SSIS and SAP

Looking for some examples link server, excel file

Looking for some examples link server, excel file

Looking for some advice.....

Looking for Solution to Remote Data Entry

Looking for Solution to Remote Data Entry

Looking for Solution to Remote Data Entry

LOOKING FOR SITE WITH NOTES ON HOW I CAN CHANGE THE KNOWLEDGE OF MSSQL I HAVE TO ORAC

Looking for simple sample

Looking for server and db monitoring scripts

looking for security guidelines to avoid sql injection risks with xquery

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than 24 hours

Looking for sample code for doing store procedures

Looking for references for querying Active Directory (AD) through SQL Server

Looking for recommended approach to merging records

Looking for real-world situation examples or tutorials on replication

Looking for RDBM reviews and ratings

Looking for RDBM reviews and ratings

Looking for RDBM reviews and ratings

Looking for query plan determination information

Looking for query help

Looking for query equivalent

Looking for Query

Looking for process Ideas

Looking for physical design suggestions...

Looking for options to fix a poorly implemented solution

Looking for opinions.....want to use SQL Server to store images

looking for non similar orders

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

query equivalent

Blog Archive

About Me