BrainBench - SQL (ANSI)
Jul 13, 2004Took a test and ran into a question. Have a look, am I even close?
View 4 Replies
ADVERTISEMENT
Question: Has Anyone Written A Tool To Convert From ANSI-89 To ANSI-92 Join Syntax?
Oct 25, 2007 A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
Thanks, in advance,
Brian
Convert ANsi-89 To Ansi-92 Outer Join
Oct 1, 2007I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:
Code Block
select t1.* from tblconfiguration t1
,tblconfiguration t2
where t1.component = 'AdjProcessUtility'
and t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'fff'
Result:
SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport
When the converted (using SQL enterprise Mgr) runs it returns no rows:
Code Block
SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')
I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.
Here's the table definition:
Code Block
CREATE TABLE dbo.tblConfiguration
(
ServerName VARCHAR(30) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ),
Value VARCHAR(255) NOT NULL
)
I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns
Here is some of the data:
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport ReportClass ReportCSV
SQLEDEV1 AdjProcessUtility ValidationReport Ids Validation
SQLEDEV1 AdjProcessUtility ValidationReport RunTime 15:06
SQLEDEV1 AdjProcessUtility ValidationReport Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ValidationReport DefaultFileName Adj_ValidationReport_MMDDYYHHMM.csv
etc.
Any help is greatly appreciated,
Sid
Error Using Non-ANSI Joins (Was Ansi)
Oct 5, 2006Hi everyone.. can anyone help me on how to solve my problem regarding on Select.. im using PB6.5 and running on MSSLQ2005 database.. i attached an image for your reference.. thnks!
View 5 Replies View RelatedSQL Server And SQL Exams And Brainbench
Mar 28, 2004Can anyone tell me where to find a site with Braindumps specifically for Brainbench exams SQL ANSI and SQL Server 2000? Even though they are "open book", they are more challenging than I previously thought. Or if not, can anyone suggest some VERY useful books/materials to pass exams with Intermediate-Expert level knowledge of SQL and SQL Server?
Responses can be directed to stevengara@hotmail.com
Thanks,
Steve G.
ANSI/SQL Queries
Feb 8, 19991) What is the restriction on the max no. of tables that can be used in any one query in v7.0? In 6.5 it was a max of 16 :(
2) Are non-ANSI-style joins permitted in v7? Just that we're thinking of upgrading but 50% of our stored procs/views have the old *= syntax
Thanks,
Brad Carr
ANSI PADDING (pt.2)
Jun 13, 2000ok people, this is getting seriously frustrating! Please help!
As mentioned in a previous post, one of my batch jobs is printing fields with padding added, even when
the table column is defined as varchar.
I've been to the knowledge base, read the article on ansi padding, ran the test scripts.
But when I ran the select to display the columns,
THE OUTPUT FOR BOTH TABLES WAS IDENTICAL!!!
Apparently the SET ANSI_PADDING ON/OFF option had no effect!
What am i doing wrong? What is missing? Do i have to run the Set Ansi Padding option in the Master DB
context? Have I unknowingly overridden the option somewhere else? Must I brush up on my COBOL
for a career change?
HELP!
fjw
ANSI SQL Deviation
Aug 7, 2001My question here is whether or not MS SQL is processing joins according to the ANSI SQL standard. The situation is this, a friend of mine has the following query (The definition for the tables and the data in them are at the end of the message):
SELECT t.*, v.*
FROM Table3 T LEFT OUTER JOIN
TableView V ON T.tx = V.tx
WHERE V.tx IS NULL and V.part IN (1,2)
My friend is implying that the OUTER join takes precedence over the WHERE clause and should return ALL rows from table3 that don't exist in the view. The view, as you will see below, is a partitioned view. My contention is that the proper join processing is to join the tables first and then apply the WHERE clause to that result set (regardless of the join type). In my friend's case, he expects this query to return four rows. My expectation is that it will return zero rows, because in the result set after the join is performed there are no rows that have a NULL in the v.tx column AND 1 or 2 in the v.part column. The actual ouput agrees with my premise and my friend is sure that this is not proper ANSI SQL join processing. Is this proper ANSI join processing or is MS SQL deviating from the standard?
/************************************************** **********************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [CK_Table1] CHECK ([Part] = 1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO
CREATE TABLE [dbo].[Table2] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [CK_Table2] CHECK ([Part] = 2)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table3]
GO
CREATE TABLE [dbo].[Table3] (
[tx] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table3] WITH CHECK ADD
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[tx]
) ON [PRIMARY]
GO
--DROP VIEW TableView
CREATE VIEW TableView AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
GO
INSERT TableView (Part,Id,Tx) VALUES (1,1,1)
INSERT TableView (Part,Id,Tx) VALUES (1,2,2)
INSERT TableView (Part,Id,Tx) VALUES (1,3,NULL)
INSERT TableView (Part,Id,Tx) VALUES (2,4,4)
INSERT TableView (Part,Id,Tx) VALUES (2,5,5)
INSERT TableView (Part,Id,Tx) VALUES (2,6,NULL)
INSERT Table3 (tx) VALUES (1)
INSERT Table3 (tx) VALUES (2)
INSERT Table3 (tx) VALUES (3)
INSERT Table3 (tx) VALUES (4)
INSERT Table3 (tx) VALUES (5)
INSERT Table3 (tx) VALUES (6)
INSERT Table3 (tx) VALUES (7)
INSERT Table3 (tx) VALUES (8)
GO
Jet To Ansi SQL Converter
Dec 30, 2004I've got a weird question --
Have any of you ever come across software that can convert MS Jet SQL to ANSI standard SQL or other SQL flavors?
Could be a huge help in my next project. Please let me know if you know of anything like this!!
:)
ANSI Questions
Oct 27, 2005I have a 6.5 and 2000 db that allows over flow of data to be entered into the
fields, but I have another 2000 db that does not allow over flow of data to
be entered. How can I check the ansi_defaults in the db's? I went through
the book ol but it didn't explain how to see those values. Only how to set
it using the SET option. Which was SET ANSI_DEFAULTS off go. Does it set it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks
ANSI-89 DB JOINS
Jul 23, 2005Help....I have a DB I'm working with that I know doesn't work with theANSI-92 JOIN SYNTAX....I'm not sure how much this limits my ability todeal with the following situation, so I'm soliciting the help of aguru....I apologize for the lack of scripted table structure, but thisdatabase is embedded in an application that I have no true schema for.I have a crude diagram of the tables and some of the relationships, butI've managed to have manually mapped some of the fields in the tablesI'm working with.What I have is a table(A) that I need to join with 10 othertables.....I'm joining on an identifier in the (A) that may exist manytimes in any of the other 10 tables...and may not be in ANY of thetables.When I run this query:SELECTSAMPLES.PK_SampleUID,UDFSAMPLEDATA02.AlphaData,UDF SAMPLEDATA01.AlphaData,UDFSAMPLEDATA03.AlphaData,UDFSAMPLEDATA05.AlphaData, UDFSAMPLEDATA06.AlphaData,UDFSAMPLEDATA07.AlphaData, UDFSAMPLEDATA08.AlphaData,UDFSAMPLEDATA09.AlphaData,UDFSAMPLEDATA10.AlphaDat aFROM SAMPLES, UDFSAMPLEDATA01,UDFSAMPLEDATA02,UDFSAMPLEDATA03,UDFSAMPLEDATA05,U DFSAMPLEDATA06,UDFSAMPLEDATA07 ,UDFSAMPLEDATA08, UDFSAMPLEDATA09, UDFSAMPLEDATA10WHERE UDFSAMPLEDATA02.AlphaData<>' ' ANDUDFSAMPLEDATA01.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA02.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA03.FK_SampleUID= SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA05.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA06.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA07.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA08.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA09.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA10.FK_SampleUID = SAMPLES.PK_SampleUIDI return what appears to be the gazillion COMBINATIONS of all thefields in all the tables....they query doesn't even finish before theODBC driver I'm working with crashes my VBscript....Is there some way to take the multiple returned rows from a join andwork them all into ONE row per identifier?Any help I can garner would just make my week!TIA!J
View 3 Replies View RelatedANSI Nulls
May 2, 2006I've mentioned this before, but now it's got me stuck. I have atable-valued function that was created using QA in SQL 2000. I can'tedit it using 2005 Mgt Studio, because the property AnsiNullsStatusisn't set (apparently SQL 2000 forgot to set it when creating thefunction). Now I find I really want to edit it in SQL 2005 MS withoutgoing to SQL 2000 QA on my laptop. So, using SQL 2005 MS, I droppedand re-created the function, having obtained the code from SQL 2000 QA.That should fix this, right? When SQL 2005 re-creates the function, itwill specify whatever it needs. But no, I still can't edit it! Theproperty is still not set!Can anyone help? Please?Thanks,Jim Geissman
View 2 Replies View RelatedQuery About Ansi-Syntax
Sep 25, 2005I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.
Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?
2) If I write a query (shown below), it tries to join table A and B returning large number of rows.
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'
Why does it try to join the table B with A though there is no join specified.
DatePart Function In ANSI SQL
Jan 17, 2007Hi folks,
How can I re-write the following code in ANSI SQL code:
select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
from usmuser.usm_sli_event_data
where event_instance_id=10019
group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
event_instance_id
order by event_instance_id
Thanks for your help!
-Parul
Ansi SQL92 Question
Feb 1, 2007I have an application that uses the old join syntax instead of theSQL92 standards join syntax.I need to justify changing the code to the new standard.Is there any performance issue related to using the old syntax thatare documented?Are there any other issues that I use to justify a code upgrade?
View 6 Replies View RelatedHow To Write ANSI-standard JOIN ?
Oct 24, 2001How to use ANSI-standard JOIN to write follow query which contains two outer join ?
SELECT a.*,b.title as classification,c.title as
employees,d.username,d.password,d.role_id,d.status
FROM DBO.PROFILE a,DBO.classification b,DBO.employee c ,DBO.USER d
WHERE a.user_id = 1 and a.employee_id *=c.id and a.classification_id *=b.id
and d.id= 1
Many thanks
Table Variables - ANSI Compliant?
Jun 21, 2001This should be a relatively easy one... Are table variables, the data type, SQL (92 or 99) compliant?
TIA,
R2ro
ANSI-92 Inner Join Vs. Where Clause Syntax
Jan 11, 2005Having problems rewriting my join condition using the "inner join" syntax.
My query, working with an intersection table:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur
WHERE
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
This works fine, but i want to write it using 'inner join' style, so I tried:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u
INNER JOIN Accounts_UserRoles ur
ON
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)
Any ideas as to how I'm screwing this up would be appreciated.
Thanks,
Gordon Z
Hi Help Needed ANSI Standard Concatenation
Dec 16, 2005Hi all
Is theire any ANSI standard concatenation
i mean ANSI standard SQL statement
Ansi Nulls And Quoted Identified
Aug 8, 2005Hello everyone,SQL Server 2000. I have a database in which there are several objectswhich have ansi nuls and quoted identifier turned ON. Is there a way Ican generate a script which:(1) Can identify all objects within the database that have those twoproperties turned ON and(2) Change the properties for these objects and turn the ansi nulls andquoted identifier OFF for those objects.I am trying to avoid going throuh gazillions of objects and manuallydoing this.Thanks for any help.Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 17 Replies View RelatedIdentify ANSI/SQL-92 Non-compliant Code
Nov 4, 2006How can we scan our code and identify the ANSI/SQL-92 non-compliant code?
Is there a tool/utility to do so?
I have already tried "SQL Best Practice Analyzer" and it seems it does not cover everything.
Thanks/
ANSI NULL DEFAULT Database Option
Nov 13, 2007Hi,I am using SQL Server 2005 32 bit version. Could any one clarify methe caption database option with some examples? I am quite confusedwith the explanation given in MSDN. Kindly help me.Thanks in Advance.Om Prakash
View 1 Replies View RelatedNeed Help With Converting Old Style Subselect To ANSI Joins?
Jul 20, 2005Help!I'm trying to understand the new ANSI join syntax (after many years ofcoding using the old style). I am now working with an application that onlyunderstands ANSI syntax so I am struggling.My first (old style syntax) SQL statement below produces 60 rows:SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1,ADDRESS A6WHERE A1.ADDRESSID=A6.ADDRESSIDAND A1.CONTACTID IN(SELECT A4.CONTACTIDFROM CONTACT_LEADSOURCE A4,LEADSOURCE A5WHERE A4.LEADSOURCEID = A5.LEADSOURCEIDAND A5.DESCRIPTION = 'some_description' )AND A1.CONTACTID IN(SELECT A2.CONTACTIDFROM TICKET A2,ENROLLHX A3,EVENT A7WHERE A3.STATUS IN ('R', 'Confirmed')AND A2.TICKETID = A3.EVXEVTICKETIDAND A3.EVENTID = A7.EVENTIDAND A7.CODE IN('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))ORDER BY A1.LASTNAME ASCI am trying to convert this to the newer ANSI sytax. My second SQL statementbelow produces 67 rows (duplicates):SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)JOIN( SELECT C.CONTACTIDFROM CONTACT CJOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID= A4.CONTACTID)JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =A5.LEADSOURCEIDAND A5.DESCRIPTION ='some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTIDJOIN(SELECT C2.CONTACTIDFROM CONTACT C2JOIN TICKET A2 ON (C2.CONTACTID =A2.CONTACTID)JOIN ENROLLHX A3 ON (A2.TICKETID =A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTIDAND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))AS C3 ON C3.CONTACTID = A1.CONTACTIDCan anyone shed some light on what I am missing?cheers,Norm
View 3 Replies View RelatedHow To Save A Query Result In ANSI In SQL 2005?
Feb 28, 2007Hi
When I was using Enterprise Manager (SQL 2000) and stored my query result (to file), it was stored in Ansi Encoding.
After I upgrade to SQL Server Management Studio (2005), it seams that when I store the query result to file it's Unicode Encoded. This give me a lot of trouble, when I use other program who I must read this file. For small result set, I can open it in Notepad, save it as ANSI, and then use it in my other program. When the result set is so big that Notepad not can help me, I can't use the stored result set.
There must be a way to store my Query result in Ansi encoding, but I don't know how I can do it. Hope some one know, and can help me solve this big problem.
JF
Collation Ansi Padding And Trailing Blanks
Apr 3, 2006Hi,
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank ';
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank ';
GO
DROP TABLE t1
DROP TABLE t2
DB Engine :: Changing ANSI PADDING On A Table
Aug 3, 2012I have a question relating to the ANSI_PADDING setting on some existing tables in a SQL Server 2008 R2 database I am working with. When I generated the tables originally I basically programmatically created them by building CREATE scripts within my code. Since I did not explicitly set ANSI_PADDING to ON all these tables they seem to have been created with ANSI_PADDING as OFF. Some of these tables, which I now need to add columns to, contain varchar(n) and varbinary(n) columns.
When I try to alter the tables through Management Studio, SQL Server gives me a warning: "One or more tables have ANSI_PADDING 'off' and will be recreated with ANSI_PADDING 'on'" - this seems to be generated by the ALTER statement which by default sets ANSI_PADDING to ON. Another iteration of the same warning - "Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'".
From what I read regarding ANSI_PADDING it seems ON is definitely the way to go. I just need to know if changing the value may result in any of the existing data in the table to be changed or may have any other unintended side effect, as this may cause problems for me.
SQL 2012 :: Ignore ANSI Characters In Data Comparison?
Aug 18, 2014I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
View 4 Replies View RelatedSQL 2012 :: ANSI PADDING Off When Generating Replication Snapshot
Jul 9, 2015I am attempting to create a snapshot replication publication.
When the snapshot is generated, any table that consists of all numeric columns creates a script with SET ANSI PADDING OFF
I googled around a bit and found that if a DDL trigger is in place on the database, it will cause this to happen.
I dropped the triggers, re-created the publication and subscription, same thing.
Downgrade SQL2005 To SQL2k - Unicode ANSI Issue
Jan 26, 2007Hello,I created a script with database publishing wizard to convert a SS2005db into a SS2k db. The script has schema and data and looks very good.When I try to start the created script in SS2k-Query analyzer, I get anerror like (in german)Server: Nachr.-Nr. 105, Schweregrad 15, Status 1, Zeile 2Öffnendes Anführungszeichen vor der Zeichenfolge '�퉊䘒'.It meens - translated from german- 'opening quote before string<blabla>'In the sript it stops at this line:EXEC dbo.sp_addextendedproperty @name=N'GUID', @value=N'�퉊䘒In the script many lines before I have many other GUIDs which QAdoesn't complain, so it's an Unicode/ANSI-issue I guess. What to donow?thankscandide_sh
View 4 Replies View RelatedSQL Server 2005 Non-ansi Joins: Any Easy Solutions?
Jul 20, 2006My company wants me to research and flags or registry tricks that would allow non-ansi joins '=*' and '*=' in SQL Server 2005 with a compatiblity mode of 90 to be allowed.
The way I understand the situation is that in SQL Server 2005 with the database compatiblity set to 90, non-ansi join SQL such as the following would not work.
Select * from
Customer, Sales
Where Customer.CustomerID *= Sales.CustomerID
To work, the SQL above would have to be converted to ansi join SQL such as the following:
Select * from
Customer LEFT OUTER JOIN Sales
On Customer.CustomerID = Sales.CustomerID
Many hours would be spent browsing through millions of lines of code to find the non-ansi SQL and have changes made.
Does anyone know of any trace flaqs or registry entries that would allow SQL Server 2005 work in 90 compatiblity and still allow non-ansi =* and *= joins in SQL?
Thanks,
AIMDBA
Isql / Osql / Windows XP / Disable Automatic ANSI To OEM Conversion
Jan 18, 2006Hello to all SQL Server junkies who work with non-English characters:For people running scripts from the command line using ANSI files withspecial characters, it is very important to use isql and disable"Automatic ANSI to OEM conversion":- This only affects isql from the command line, and no guiapplications- http://support.microsoft.com/?scid=kb;EN-US;153449- Start the "Client Network Utility"C:WINDOWSsystem32cliconfg.exe- Select the DB-Library Tab- Deselect "Automatic ANSI to OEM conversion"- Click OK or ApplyOr inject this registry entry:[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer ClientDB-Lib]"AutoAnsiToOem"="OFF"Here are some useful lines from a batch script to query the currentvalue of the registry and reset if necessary. This is tested onWindows XP. It will query the registry, throw away the first threelines of output, and return the value of the third field on the fourthline. Delims lists one tab character and one space character. Type thefollowing all on one line:@FOR /F "SKIP=3 TOKENS=3 DELIMS= " %%A IN ('REG QUERYHKLMSOFTWAREMicrosoftMSSQLServerClientDB-Lib /v AutoAnsiToOem') DO@SET AUTOANSITOOEM=%%AType the "reg add" line all on one line:@IF /i "%AUTOANSITOOEM%" EQU "ON" (@ECHO************************************************** **********************@ECHO ****@ECHO **** We need to disable "Automatic ANSI to OEM conversion"@ECHO **** Please seehttp://support.microsoft.com/?scid=kb;EN-US;153449@ECHO **** This only affects isql from the command line@ECHO ****@ECHO************************************************** **********************@REMREG ADD HKLMSOFTWAREMicrosoftMSSQLServerClientDB-Lib /vAutoAnsiToOem /t REG_SZ /d OFF)Alternatively, you must use Unicode script files and osql.PS: Thank you to Erland Sommarskog for http://www.sommarskog.se and Robvan der Woude for http://www.robvanderwoude.com
View 1 Replies View RelatedTransact SQL :: Alter Table Set Quoted Identifier On And Set Ansi Null
May 27, 2015I have existing table which is having Set Quoted Identifier Off and Set Ansi Null
Now I want to change those setting so Is there any alter statement for the same?
Also Let's say At my database level If those settings are off and If I convert it to ON then It is not taking effect on existing tables SP which are already build.
Use ANSI Join Or Old Style Join?
May 20, 2006Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.
View 1 Replies View Related