Conversion of non Ansi standard queries to ANSI Standard queries

Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Contradiction in SQL Server 2000 Licensing FAQ

Hello,
I have a 4 CPU server running SQL Server 2000 Standard Edition. I have
4 SQL Server 2000 Standard CPU Licenses for this server.
My server has been configured as case-sensitive. We have bought a
piece of third party software that requires the SQL server to be
case-insensitive. However, we do not want to risk changing our
existing installation as it would probably require a great deal of
regression testing.
One options appears to be to add a second instance of SQL server to
the same server, but install this instance as case-insensitive.
I am trying to find out whether our current licensing supports this.
From http://www.microsoft.com/sql/howtobuy/faq.asp
"A Processor license gives you the right to install any number of
copies of SQL Server 2000 on a single computer"
but further down:
"With SQL Server 2000 Enterprise Edition, you can install multiple
instances of SQL Server on the same computer without having additional
licenses. Although SQL Server 2000 Standard Edition will technically
support multiple instances, each instance requires a separate
license."
However http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
states:
"Under the Per Processor licensing model, you may install and run any
number of copies (or "instances") of the SQL Server software on a
server, provided that the required number of Processor licenses has
been acquired."
Does anyone know which is currect?
Thanks,
RichardStandard edition will support upto 4 cpus and multiple
instances, but you need separate license for another
instance of sql server
You don't need to buy separte instance licenses if you
have bought Enterprise Editon per processor license.
>--Original Message--
>Hello,
>I have a 4 CPU server running SQL Server 2000 Standard
Edition. I have
>4 SQL Server 2000 Standard CPU Licenses for this server.
>My server has been configured as case-sensitive. We have
bought a
>piece of third party software that requires the SQL
server to be
>case-insensitive. However, we do not want to risk
changing our
>existing installation as it would probably require a
great deal of
>regression testing.
>One options appears to be to add a second instance of SQL
server to
>the same server, but install this instance as case-
insensitive.
>I am trying to find out whether our current licensing
supports this.
>From http://www.microsoft.com/sql/howtobuy/faq.asp
>"A Processor license gives you the right to install any
number of
>copies of SQL Server 2000 on a single computer"
>but further down:
>"With SQL Server 2000 Enterprise Edition, you can install
multiple
>instances of SQL Server on the same computer without
having additional
>licenses. Although SQL Server 2000 Standard Edition will
technically
>support multiple instances, each instance requires a
separate
>license."
>However
http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
>states:
>"Under the Per Processor licensing model, you may install
and run any
>number of copies (or "instances") of the SQL Server
software on a
>server, provided that the required number of Processor
licenses has
>been acquired."
>Does anyone know which is currect?
>Thanks,
>Richard
>.
>|||> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
This was changed about half a year ago, as far as I understand.
Richard, I'm fairly certain that you can have several on the same license. But you should really
check with MS.
Speak to MS about licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:02e601c39cc2$49861ba0$a301280a@.phx.gbl...
> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
> You don't need to buy separte instance licenses if you
> have bought Enterprise Editon per processor license.
>
> >--Original Message--
> >Hello,
> >
> >I have a 4 CPU server running SQL Server 2000 Standard
> Edition. I have
> >4 SQL Server 2000 Standard CPU Licenses for this server.
> >
> >My server has been configured as case-sensitive. We have
> bought a
> >piece of third party software that requires the SQL
> server to be
> >case-insensitive. However, we do not want to risk
> changing our
> >existing installation as it would probably require a
> great deal of
> >regression testing.
> >
> >One options appears to be to add a second instance of SQL
> server to
> >the same server, but install this instance as case-
> insensitive.
> >
> >I am trying to find out whether our current licensing
> supports this.
> >
> >From http://www.microsoft.com/sql/howtobuy/faq.asp
> >
> >"A Processor license gives you the right to install any
> number of
> >copies of SQL Server 2000 on a single computer"
> >
> >but further down:
> >
> >"With SQL Server 2000 Enterprise Edition, you can install
> multiple
> >instances of SQL Server on the same computer without
> having additional
> >licenses. Although SQL Server 2000 Standard Edition will
> technically
> >support multiple instances, each instance requires a
> separate
> >license."
> >
> >However
> http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
> >states:
> >
> >"Under the Per Processor licensing model, you may install
> and run any
> >number of copies (or "instances") of the SQL Server
> software on a
> >server, provided that the required number of Processor
> licenses has
> >been acquired."
> >
> >Does anyone know which is currect?
> >
> >Thanks,
> >
> >Richard
> >.
> >

Contains 2 Column and / or FTS

Thanks for any help!
SQL 2000 Standard Edition with SP3. The title and document column are both
Full text indexed. The problem I'm trying to solve is how to get the below
stored procedure to search BOTH columns for the criteria entered.
Search Example:
"pin*" or "Yello*" Returns 2 rows as desired.
"pin*" and "yello*" Returns no results which is not desired.
However "Pin*" and "Gree*" does return results which is also desired.
I'm trying to build a procedure which will pull matched words from BOTH
columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
work in Microsoft, but currently when entering the "pin*" and "yello*"
search, it looks for both those terms in the SAME colum, and will not join
them so to speak.
A FreeText search would always return me the desired results, except it
does not seem to do partial term searches or wildcards, unless I'm missing
something.
I've found and seen quite a few bits of code on the internet such as this
http://www.experts-exchange.com/Data...20705253.html, but they don't seem to quite fit my needs.
Thanks a million!!
CREATE TABLE [dbo].[Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) ,
[Submitter] [varchar] (100) NOT NULL ,
[RevisedBy] [varchar] (100) NOT NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into document(Title, submitter, Revisedby, document) values ('Pink
Green',
'John Henry', 'Steven Smith', 'Orange')
insert into document(Title, submitter, Revisedby, document) values ('Silver
Blue',
'Kelly Owen', 'John Henry', 'Yellow')
GO
ALTER PROCEDURE SearchDoc2
(
@.Word varchar (300) = Null,
@.SubmitterID varchar (100) = NULL,
@.systemID varchar (100) = NULL
)
as
SELECT Title,
SubmitterID,
RevisedBy
FROM tblDocument
where (Contains(*, @.word) or @.Word IS NULL)
and
(systemID = @.SystemID or @.SystemID IS Null) and
((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
(RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
Jeff,
Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
Below are modified code examples from the above blog entry to demonstrate
how to handle both the AND or OR operators. Note, you and use dynamic SQL
code in the WHERE clause to handle both conditions in one stored proc. Also,
keep in mind that with the use of two CONTAINSTABLE clauses, this may not
perform well against large (>1 million rows) FT-enabled tables as each
CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
use Northwind
SELECT e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and -- Note, AND operator.
B.[KEY] = e.EmployeeID
-- vs.
use Northwind
SELECT distinct e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID or -- Note, OR operator.
B.[KEY] = e.EmployeeID
For the OR operator, you will need to add the DISTINCT operator to prevent
dups in your results.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
> Thanks for any help!
> SQL 2000 Standard Edition with SP3. The title and document column are
> both
> Full text indexed. The problem I'm trying to solve is how to get the
> below
> stored procedure to search BOTH columns for the criteria entered.
> Search Example:
> "pin*" or "Yello*" Returns 2 rows as desired.
> "pin*" and "yello*" Returns no results which is not desired.
> However "Pin*" and "Gree*" does return results which is also desired.
> I'm trying to build a procedure which will pull matched words from BOTH
> columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
> work in Microsoft, but currently when entering the "pin*" and "yello*"
> search, it looks for both those terms in the SAME colum, and will not join
> them so to speak.
> A FreeText search would always return me the desired results, except it
> does not seem to do partial term searches or wildcards, unless I'm missing
> something.
> I've found and seen quite a few bits of code on the internet such as this
> http://www.experts-exchange.com/Data...20705253.html,
> but they don't seem to quite fit my needs.
> Thanks a million!!
> CREATE TABLE [dbo].[Document] (
> [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (100) ,
> [Submitter] [varchar] (100) NOT NULL ,
> [RevisedBy] [varchar] (100) NOT NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into document(Title, submitter, Revisedby, document) values ('Pink
> Green',
> 'John Henry', 'Steven Smith', 'Orange')
> insert into document(Title, submitter, Revisedby, document) values
> ('Silver
> Blue',
> 'Kelly Owen', 'John Henry', 'Yellow')
> GO
>
> ALTER PROCEDURE SearchDoc2
> (
> @.Word varchar (300) = Null,
> @.SubmitterID varchar (100) = NULL,
> @.systemID varchar (100) = NULL
> )
> as
>
> SELECT Title,
> SubmitterID,
> RevisedBy
>
> FROM tblDocument
> where (Contains(*, @.word) or @.Word IS NULL)
> and
> (systemID = @.SystemID or @.SystemID IS Null) and
> ((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
> (RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
>
>
|||Thanks for the reference John.
I read it over well and tried the examples which I've somewhat tried before.
Aparently when using the [and] operator with contains[table], it looks for
both "words" to exist in the same column, and not scross columns.
I think perhaps the FreeTextable will suit our needs, it's just a shame that
it does not support wildcards yet.
Thanks,
Jeff
"John Kane" wrote:

> Jeff,
> Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
> SQL Server FTS across multiple tables or columns
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> Below are modified code examples from the above blog entry to demonstrate
> how to handle both the AND or OR operators. Note, you and use dynamic SQL
> code in the WHERE clause to handle both conditions in one stored proc. Also,
> keep in mind that with the use of two CONTAINSTABLE clauses, this may not
> perform well against large (>1 million rows) FT-enabled tables as each
> CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
> use Northwind
> SELECT e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and -- Note, AND operator.
> B.[KEY] = e.EmployeeID
> -- vs.
> use Northwind
> SELECT distinct e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID or -- Note, OR operator.
> B.[KEY] = e.EmployeeID
> For the OR operator, you will need to add the DISTINCT operator to prevent
> dups in your results.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
>
>
|||Jeff wrote on Mon, 19 Sep 2005 11:19:06 -0700:

> Thanks for the reference John.
> I read it over well and tried the examples which I've somewhat tried
> before.
> Aparently when using the [and] operator with contains[table], it looks for
> both "words" to exist in the same column, and not scross columns.
> I think perhaps the FreeTextable will suit our needs, it's just a shame
> that it does not support wildcards yet.
You can achieve your result using Contains, but it requires more processing:
select * from mytable where contains(*,'"pin*"') and contains(*,'"yellow*"')
This will find all rows with pin* in the FTS index, and all rows with
yello*, and then combine them together to find all rows that have both terms
in either column.
However, from a performance point of view this may not be satisfactory as
there is an FTS search for each word rather than all at once. In my own
system I maintain an extra column that is a contenation of the 6 columns
that I use in my searches - so if I want a search just on book titles I
would use Contains(title,@.words) but if I wanted the words to appear in any
columns then I'd use Contains(keywords,@.words). It does mean that when data
is changed the keywords field needs to be rebuilt, but all data is processed
from files via an in-house application so this is done automatically.
Additional storage is required for the extra column, but I'd rather use up a
bit more of my disk space than have slow FTS results (disk space is after
all relatively cheap compared to the perceived performance of a web site
search by customers).
Dan