Creating view using Openquery and embedded quotes

• rippersoftware

  SSC Veteran

  Points: 204

  More actions

  October 17, 2003 at 9:52 am

  #160828

  When creating a view, you state your selection criteria which can include character constants that are single quoted, such as 'ALL'.

  In the openquery function, the link server is the first parameter and the query statement is the second parameter. The statement must be a character string which means single quotes.

  How do you embed a quoted character constant in a qouted string, like the following:

  'Select * from customer where state = 'AL''

  Thanks.

• rs

  SSCrazy

  Points: 2819

  More actions

  October 17, 2003 at 10:38 am

  #478665

  Try This

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  CREATE VIEW dbo.vw_test

  AS

  SELECT * FROM OPENQUERY(TEST,"Select * from customer where state = 'AL'")

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

• Tony Bater

  SSCrazy

  Points: 2150

  More actions

  October 20, 2003 at 2:37 am

  #478745

  You need to embed the quoted constants within another pair of single quotes:

  CREATE VIEW dbo.vw_test

  AS

  SELECT * FROM OPENQUERY(TEST,'Select * from customer where state = ''AL'' ')

  GO

  Tony Bater

  
  Tony