Set QUOTED_IDENTIFIER And ANSI_NULLS On All Objects...
Sep 6, 2006
Is there any way via T-SQL to run through a database and ensure that QUOTED_IDENTIFIER and ANSI_NULLS is set for all stored procs and functions etc on a database without having to recreate every proc / fn ?
The reason this is an issue is I'm trying to take advantage of indexed views to get a performance increase in an application - but not all of the procs/fn's have historically been created with ANSI_NULLS and QUOTED_IDENTIFIER set - so any proc that's not set correctly will fail once the indexed view has been created.
I could I suppose, script out all the procs / functions and do a bit of search and replace to ensure that these are set correctly by recreating all procs and functions - but I'm trying to avoid doing that as I've over 500 databases to upgrade - a metadata hack may be preferable.
Thoughts or comments or possible alternative approaches are welcome...
View 3 Replies
ADVERTISEMENT
Dec 2, 2007
does anyone know how to keep QA from adding the lines setting thesetwo options on and off along with blank lines at the beginning and endof every object you edit? i have searched quite a bit on this buthaven't been able to come up with anything.
View 1 Replies
View Related
Jul 20, 2005
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."error after creating a view.We wanted a composite unique constraint that ignored nulls, so we setup a view using the following script:/* --- start --- */BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITGOCREATE VIEW vw_MyViewWITH SCHEMABINDINGASSELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULLGO/* --- end --- */and then added the constraint to the new view/* --- start --- */CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ONvw_MyView(Col1, Col2)GO/* --- end --- */I thought we were doing fine, 'til we started running some DELETEstored procedures and got the above error. The error also citedARITHABORT as an incorrect setting until we ran this script:/* --- start --- */USE masterDECLARE @value intSELECT @value = value FROM syscurconfigsWHERE config = 1534SET @value = @value | 64EXEC sp_configure 'user options', @valueRECONFIGURE/* --- end --- */TIA to anyone kind enough to shed some light on this for me. Is theresomething we should have done differently in creating the view andindex? If not, what's the procedure for working through thesesettings errors?I've read through some other threads on this subject, but didn'treally find what I was looking for. Thanks again for any help. Wouldbe appreciated.-matt
View 3 Replies
View Related
Jul 20, 2005
Hello!How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT andthen switch it ON straight afterwards. So I can insert strings likeO'Neil etc.It's my only design option at the moment. Everything works fine and asfar as I can see, it's only set to OFF for the currentconnection/session so no other users will run into problems at thesame time if they need it ON, right?Thanks Martin
View 4 Replies
View Related
Mar 24, 2008
OK, so I know that following works if I want update/insert the value "joe's bar" into the table.
set SET QUOTED_IDENTIFIER on
update #temp set id=4 where name ='joe''s bar'
The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)
View 4 Replies
View Related
Jul 21, 2006
I have a very strange problem, it only happen to one SQL Server, otherSQL Server seems to be fineI got the following error when trying to run a sp against one of theSQL Server:SELECT failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct foruse with indexed views and/or indexes on computed columns and/or querynotifications and/or xml data type methods.If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,everything works fine, but the questions is why should I do that? andwhy it only happen to only one SQLServer ? The database option onQUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)Thanks in advance.JohnEnclose is the statement that create the databaseif db_id('testdb') is not nulldrop database [testdb]gobeginUSE [master]CREATE DATABASE [testdb] ON PRIMARY( NAME = N'testdb',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb.mdf' ,SIZE = 8192KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'testdb_log',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb_log.ldf' ,SIZE = 29504KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASEXEC dbo.sp_dbcmptlevel @dbname=N'testdb',@new_cmptlevel=90ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFFALTER DATABASE [testdb] SET ANSI_NULLS OFFALTER DATABASE [testdb] SET ANSI_PADDING OFFALTER DATABASE [testdb] SET ANSI_WARNINGS OFFALTER DATABASE [testdb] SET ARITHABORT OFFALTER DATABASE [testdb] SET AUTO_CLOSE OFFALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ONALTER DATABASE [testdb] SET AUTO_SHRINK OFFALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ONALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFFALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBALALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFFALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFFALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFFALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFFALTER DATABASE [testdb] SET ENABLE_BROKERALTER DATABASE [testdb]SETAUTO_UPDATE_STATISTICS_ASYNC ONALTER DATABASE [testdb]SETDATE_CORRELATION_OPTIMIZATION OFFALTER DATABASE [testdb] SET TRUSTWORTHY OFFALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLEALTER DATABASE [testdb] SET READ_WRITEALTER DATABASE [testdb] SET RECOVERY FULLALTER DATABASE [testdb] SET MULTI_USERALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUMALTER DATABASE [testdb] SET DB_CHAINING OFFend
View 4 Replies
View Related
Jul 13, 2007
When I execute a SET QUOTED_IDENTIFIER statement, does it only apply to the current batch or current connection? I noticed it doesn't change the "Quoted Identifiers Enabled" setting in the Options page of the Database Properties in Management Studio.
Thanks,
-Dave
View 5 Replies
View Related
Jul 31, 2006
Our client's application software requires all stored procedures tohave quoted_identifier set a certain way. I've tripped over this afew times and promptly fixed it, but this morning, I had totroubleshoot a case where someone else tripped over it. In such asituation, how can I identify which SP(s) have it set the wrong way?(No huge rush, the someone-else has already fixed the recent case)SQL 2K, SP3
View 2 Replies
View Related
Dec 13, 2005
I am working for a client that would has XML data being passed into a Service Broker queue.
View 8 Replies
View Related
Sep 20, 2006
When I want to delete a data from a table that this tabl has a triggerand this trigger reached another tables to delete the data in cursor Ihave this messeage:DELETE failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'.My trigger :CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]FOR DELETEASBEGINDECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINCbigintSELECT @rows_affected = @@ROWCOUNTIF @rows_affected = 0RETURN -- No rows changed, exit triggerBEGINDECLARE Miktar CURSOR FORSELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINCFROM deletedOPEN MiktarFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCWHILE @@fetch_status = 0BEGINSET QUOTED_IDENTIFIER ONDELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINCAND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1SET QUOTED_IDENTIFIER OFFPRINT @DEPOBKINCFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCENDCLOSE MiktarDEALLOCATE MiktarENDEND
View 6 Replies
View Related
Sep 21, 2007
What does this mean and do?
SET ANSI_NULLS ON
View 2 Replies
View Related
Nov 26, 1999
Hi,
I figure that my query below should return the records that I've inserted into test1 and test2 after I turn ansi_nulls off for my session. But the query does not return any records. Any ideas?
create table test1 (id int, create_date datetime null)
create table test2 (id int, create_date datetime null)
insert test1 values (1,null)
insert test2 values (1,null)
set ansi_nulls off
select *
from test1 t1 join test2 t2 on t1.id = t2.id
where t1.create_date = t2.create_date
View 2 Replies
View Related
Nov 20, 2002
Could someone explain why these queries give two seemingly different
results?
set nocount on
set ansi_nulls off
create table #tmp1 ( col1 char(1) )
insert into #tmp1 values (null)
insert into #tmp1 values ('1')
select case when col1 <> '1' then 1 else 0 end from #tmp1
TIA...
View 5 Replies
View Related
Jul 20, 2005
Hi All,I'm converting some stored procs from Sybase to SQL Server, and I'mhaving serious problems with Null comparisons.When I run the code below on Sybase, it returns both rows. When I runit on MS SQL Server, it returns nothing. I have set ANSI_NULLS off inthe code (and on the session through Query Analyzer), but it doesn'tseem to make any difference. Am I missing something?---------------set ANSI_NULLS offdrop table #TestNullcreate table #TestNull (Field1 varchar(10), Field2 varchar(10))insert into #TestNull values (1, null)insert into #TestNull values (1,1)declare @TestVar varchar(10)select @TestVar = Nullselect * from #TestNull where Field1 = @TestVar---------------Thanks in advance,Saul
View 4 Replies
View Related
Jul 10, 2001
Im having trouble accessing a linked/remote server from a powerbuilder app.
I can run the stored proc from QueryAnalyzer, but from the app I get the error message - 'heterogenous query must have ANSI_NULLS set option ON, set it and retry'.
Is the ANSI_NULLS option set for each db, or each server? And how can I check a DB or Server to see what the setting for ANSI_NULLS is? Does the local and remote/linked server/db need this option ON?
Any idea why this remote query works form QA but not from an application?
Thanks-
Scott
View 1 Replies
View Related
Oct 19, 2006
I have stored procedure:
EXEC sp_addlinkedsrvlogin @FailedRegionServerName, 'false', NULL, 'sa', 'pass'
DECLARE @a varchar(100)
SET @a = @FailedRegionServerName + '.Ithalat.dbo.Product'
DECLARE @s varchar(100)
SET @s = ' SELECT * FROM ' + @a
EXEC ( @s )
When I execute it I get the error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work
Then I tried:
DECLARE @s varchar(300)
SET @s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @a
EXEC ( @s )
I still got the error.
WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...
View 12 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
May 8, 2008
As noted on the msdn the ANSI_NULLS for a stored procedure are set at time off creation (http://msdn.microsoft.com/en-us/library/aa259229(SQL.80).aspx).
Using:
- SQL server 2000
My question is; How do i get the details of a sp and display the ANSI_NULLS setting for it.
("select * from information_schema.routines" doesnt shows this information)
Is this possible in SQL 2000 (or 2005)?
View 3 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
May 8, 2001
I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):
SELECT DISTINCT a.*
FROM LOCATION a,
LinkServer.MC_Card.webuser.LOCATION b
WHERE a.location_number = b.location_number
But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:
Error 7405: Heterogeneous queries require ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Here is the Stored Procedure:
/****** Object: Stored Procedure dbo.spELRMCcardXtionByDate
Script Date: 4/24/2001 11:51:27 AM ******/
CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
-- declare @dcid nvarchar(255)
-- set @dcid = '1032'
SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number)
LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY STORE.[Str#]
-- and E.card_number IS NOT NULL
GO
Any help greatly appreciated.
Thanks,
Bruce
View 2 Replies
View Related
May 14, 2001
I am trying to create a stored procedure that updates a table on another server. It give the me the error about requiring ANSI_NULLS and WARNINGS being set. How can I set these if they are not already set? I tried setting them within the stored procedure, but does not appear to be working. Unless I am doing something wrong. I am trying to add SET ANSI_NULLS ON and doing the same thing for WARNINGS. Any thougts or suggestion on what to do? Thanks for the help
View 2 Replies
View Related
Sep 5, 2007
Hi,
I have a problem with linked servers.
I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.
So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.
But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.
If anyone can help me, I'd be veri greatfull
Best regards, Ariel
View 2 Replies
View Related
Nov 17, 2007
I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.
I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
or else my vb6 recordset crashes.
When I created the sproc, I did what every one was telling me to do in the forums by putting in the
SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure usp_SprocName
AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
Can someone help me?
View 4 Replies
View Related
Oct 26, 2006
Hello,
I'm using the SQL Native Client to connect my VB6 application to my SQL Server 2005 database. My SQL Server 2005 database has ANSI_NULLS turned off. I have a query embedded in my VB6 application that uses the syntax "fieldName = NULL" in the WHERE clause. When I execute the query via the SQL Native Client, the query returns zero rows. When I execute the same query via the old OLEDB driver, the query returns many rows. If I change my query to "fieldName IS NULL" syntax, the problem goes away. However, I am more interested in figuring out why ANSI_NULLS are turned on when using the SQL Native Client even though my database has them turned off. Is there a connection string property that I can use with the SQL Native Client to ensure that the query is executed with ANSI_NULLS off?
Thanks
View 15 Replies
View Related
Oct 1, 2007
Hi, i have problem as subject says.
Db has table with 3 columns, ID, Key and Val. ID is primary key, Key has unique index and Val simple holds value in text format.
I have created DAL layer using .netTiers and CodeSmith. Generated procedures.sql has before every procedure set ANSI_NULLS to OFF.
When i read rows from table i print them on screen. When user changes value, that should also be updated in database.
When i select entity, its value is changed.
Here is code snipper.
Code Blockentity.Key = key;
entity.Value = value;
TransactionManager transactionManager = DataRepository.Provider.CreateTransaction();
try
{
transactionManager.BeginTransaction();
retVal = DataRepository.TestTableProvider.Update(entity);
transactionManager.Commit();
}
catch
{
transactionManager.Rollback();
throw;
}
I got an exception with message below:
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Also, class which hold previous code snipper supports caching, by using some kind of SqlCacheManager, which is above SqlCacheDependecy class. Database service broker is started by
Code Block
ALTER DATABASE <DB_NAME> SET ENABLE_BROKER
Server is SqlExpress 2005.
Application is in ASP.NET 2.0.
I also noticed next.
On first run, previous code passes without errors. On second run, update error appears.
This is log from sql server.
Code BlockQuery notification delivery could not send message on dialog '{822C7891-736E-DC11-836B-005056C00008}.'. Delivery failed for notification '<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationhttp://schemas.microsoft.com/SQL/Notifications/QueryNotification">http://schemas.microsoft.com/SQL/Notifications/QueryNotification</A< A>>" id="1" type="change" source="database" info="restart" database_id="13" sid="0xE7C0751C9F7F6C4D9423096BBCC7FB69"><qn:Message>edd3e2dd-11ed-4d92-a0f4-5c674a90aecf;8b2095663cc6a9c297120e4c94d488555e97e54d</qn:Message></qn:QueryNotification>' because of the following error in service broker: 'The conversation handle "822C7891-736E-DC11-836B-005056C00008" is not found.'
Need fast answer.
Thanks in advance.
View 5 Replies
View Related
Jul 20, 2005
When I perform a query on a linked Oracle server in the Query analyser Ihave noprboblem' to perform this query.However, when I create this query in a stored procedure I get a compilationerrorwhen saving this procedure. (Not when compiling; it has no errors)Server: Msg 7405, Level 16, State 1, Line 1Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics.Enable these options and then reissue your query.When I create a dynamic SQL statement then I can save this stored procedurewhen I run the stored procedure this same error happens.What do I have to do.Arno de Jong, The Netherlands
View 1 Replies
View Related
Aug 27, 2001
Hi, there,
I use VB to make an dll file and register on my local PC, then use sp_OACreate to create OLE object on local SQL server, it works fine. But when I register that dll on other SQL server, it dosen't work, anybody have idea about this? Thank you.
Tony
View 1 Replies
View Related
Jul 17, 2000
I see these type of objects in Books on Online. I was just wondering
if anyone knows how to use these objects, seems to have lot of good
information. Could these objects be called in VB?
--------------------------------
CreateDate Property (SQL-DMO)
The CreateDate property indicates the date and time the referenced SQLServer object was created.
Applies To
Database Object Table Object
DBObject Object TransactionLog Object
Default Object Trigger Object
Rule Object View Object
StoredProcedure Object
Syntax
object.CreateDate
Part Description
object Expression that evaluates to an object in the Applies To list
Remarks
The string returned is formatted by using the locale setting of the workstation.
Data Type
String
Modifiable
Read-only
Prototype (C/C++)
HRESULT GetCreateDate(SQLDMO_LPBSTR pRetVal);
--------------------------------------------------------
View 1 Replies
View Related
Sep 21, 2007
Anyone know how to grant a user to see all data in sys.objects view?
Thanks.
View 1 Replies
View Related
Sep 21, 2007
Anyone know how to grant a user (who is not a sa) to see all data in sys.objects view?
Thanks.
View 1 Replies
View Related
Jan 25, 2007
Hi everyone,
Primary platform is Framework 2.0 under 32-bit
Production platform will be Framework 2.0 under 64-bit.
I'm concerned about what object should I use in order to launch DTS 2000/7.0 from our .Net service.
Currently it tested fine from a 32-bit environment (sql server 2005 as backend).
Reference: "Microsoft DTSPackage Object Library"
Physical file: dtspkg.dll
My wonder, is there any limitation when it's gonna to work on 64-bit?
Generally speaking, any limitation/issue/drawback for COM objects under 64 bit??
In my project properties appears Any CPU, x86, x64. I've got now Any CPU.
Thanks for your advices,
View 13 Replies
View Related
Apr 4, 2007
I know it's "best practice" to dispose ado.net objects, but does it make a big difference if just the connection is closed? In other words, is the code below good enough or should the DataAdapter & Command be explicitly closed?using (SqlConneciton cn = new SqlConnection(connstr)){ SqlDataAdapter da = new SqlDataAdapter(sql,cn);DataSet ds = new DataSet();da.Fill(ds); SqlCommand cmd = new SqlCommand(someOtherSql,cn);cmd.ExecuteNonQuery(); }
View 2 Replies
View Related
Jun 16, 2008
Hi,
I am a Database called "Cache" user and in it and when creating a new table I can create a column of type Object.
So basically, I can create an object eg. Address that have the following items StreetNo, StreetName, PCode somewhere in my database; then create a table called eg. Employees that have a column called EmployeeAddress of type Address (as my Address Object). Can this be done in SQL Server!?
Thanks.
View 4 Replies
View Related
