Dealing with apostrophies in strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dealing with apostrophies in strings

Author

Topic

ashraff87
Starting Member

17 Posts

Posted - 2008-08-05 : 08:05:26

Hi im urrently trying to execute a query like this:


SELECT *
FROM
OPENROWSET('SQLNCLI','serverA';'sa';'pword'
,'

EXECUTE [MyDB].[dbo].[Get_Sales] 
   
   '2005-06-30 00:00:00.000'
  
   
  ') 
WHERE price < 10

As you can see im running a statment in the openrowset command that needs to be inbtween ' ', but since im having to give the stored proc a parameter which requires apostrophies also, the result is that the date is infact not in string format and it causes an error. So my question is how do you use apostrophies within a string without it breaking?

PollyMorph
Starting Member

5 Posts

Posted - 2008-08-05 : 08:21:03

There are a couple of ways to do it...

You can use variables instead and build a string. or you can use double apostrophies. 'Select ''test'' from tablename'

I would rather use the following method. I have used it previously to do something similar.

DECLARE @ExcelSheetName AS NVARCHAR(250)
SET @ExcelSheetName = 'c:\demoexcel.xls'

SET @SQLString = N'SELECT *, 0 , @MessageContent, @CampaignId, @Priority, @CustomerId, 0, 0, @AddedById, GETDATE() FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @FilePath + ''',''SELECT * FROM [' + @ExcelSheetName + ']'')'
SET @ParmDefinition = N'@MessageContent AS NVARCHAR(250), @CampaignId AS INT, @Priority AS INT, @CustomerId AS NVARCHAR(250), @AddedById AS INT'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @MessageContent=@MessageContent, @CampaignId=@CampaignId, @Priority=@Priority, @CustomerId=@CustomerId, @AddedById=@AddedById

Please just remember to declare all the variables you want to pass.

Polly.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 08:21:40

To put quotes aroung a string that contains a single quoute, you need to "escape" the quote by preceding it with another single quote:
'O''Hare' shows up like: O'Hare

Be One with the Optimizer
TG

ashraff87
Starting Member

17 Posts

Posted - 2008-08-05 : 08:43:42

OK so i modified it to use double apostrophies its still not running, this is the code now


SELECT *
FROM
OPENROWSET('SQLNCLI','serverA';'sa';'pword'
,'

EXECUTE [MyDB].[dbo].[Get_Sales] 
   
   ''2005-06-30 00:00:00.000''
  
   
  ') 
WHERE price < 10

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-05 : 08:59:22

You never want to get rid of apostophies like this. Use proper parameterised statements.

ashraff87
Starting Member

17 Posts

Posted - 2008-08-05 : 09:13:13

yeh it is messy, ok could i please have an example loztinspace

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources