June 2, 2008 at 1:44 pm
Hi, I am building a table with a column that stores query's from my reporting service deck. However; I am running into a problem with single quotes in my query's. I was trying to use the replace function to to space out the quotes or to format them for insertion into my table something like this:
insert into table1 (query)
values('select replace('select * from table 2 where something in ('a', 'b', 'c')', ''', ''))
I am getting an 'Incorrect Syntax error near 'a'' on line 3. Does someone know of a way I can get around this in order to insert my query's into my table?
Thank you.
June 2, 2008 at 1:58 pm
Hi Marcus,
your query looks a little messed up.
Try to rewrite it from scratch.
To replace singlequotes, use this format: REPLACE('stringxy' ,'''','')
Best Regards,
Chris Büttner
June 3, 2008 at 4:57 am
I think what you're trying to do is insert a SQL string into a varchar text field.
You have to replace all the single quotes with '' to escape them [that's 2 single quotes]
Like so:
INSERT INTO
FOOD(Query)
VALUES
('Wheatie''s')
June 3, 2008 at 4:58 am
Also, I know that Wheaties has no apostrophe....
June 3, 2008 at 5:20 am
The best way to do this would be to use variables in your stored proc
instead of
insert into table1 (query)
values('select replace('select * from table 2 where something in ('a', 'b', 'c')', ''', ''))
try putting your values into variables
declare @ValueA varchar(20)
declare @ValueB varchar(20)
declare @ValueC varchar(20)
Insert into Table2
values (@ValueA, @ValueB, @ValueC)
use the variables as input parameters for your stored procedure and this should solve your problem
June 3, 2008 at 4:17 pm
June 3, 2008 at 4:53 pm
Here is one way:
-- Setup the variable for testing
Declare @value varchar(1000);
Set @value = 'select * from table 2 where something in (''a'', ''b'', ''c'')';
Select @value; -- this will look like select * from table 2 where something in ('a', 'b', 'c')
Select quotename(@value, ''''); -- 'select * from table 2 where something in (''a'', ''b'', ''c'')
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 4, 2008 at 7:51 am
Hello all and thanks for responding, I wasn't able to get this to work for me. We are building a table that contains all our query's on our reporting services deck. An example of a query would be:
SELECT [Key Measure], Indicator, AVG(Target) AS Target, AVG(Results) AS Results, Period,
CASE WHEN Period = 'JAN' THEN 01 WHEN Period = 'FEB' THEN 02 WHEN Period = 'MAR' THEN 03 WHEN Period = 'APR' THEN 04 WHEN Period = 'MAY'
THEN 05 WHEN Period = 'JUN' THEN 06 WHEN Period = 'JUL' THEN 07 WHEN Period = 'AUG' THEN 08 WHEN Period = 'SEP' THEN 09 WHEN Period =
'OCT' THEN 10 WHEN Period = 'NOV' THEN 11 WHEN Period = 'DEC' THEN 12 WHEN Period = 'YTD' THEN 13 END AS [Monthly Order]
FROM vu_All_Data_DashboardSummary_08_WithTargets
WHERE (NPA IN ('Ontario', 'Ontario Aliant', 'Ontario Bell'))
GROUP BY [Key Measure], Indicator, Period
what I would like to do is write an insert statement that I can copy the query in as a varchar data type into the value of the query column. The problem I was running into was the single quotes. We have a manual work around. Just trying to figure out a way to do it through scripting, but haven't been able to. Thanks for all responses.
June 4, 2008 at 8:15 am
Just do a search and replace for each single quote (') and make it 2 single quotes (''). 2 single quotes escapes one
June 4, 2008 at 11:53 pm
James is right. If your query is not in any kind of variable, then only way out is to copy it in any text editor and replace 1 single quote (') with 2 single quotes ('').
June 5, 2008 at 6:46 am
Hello gents, that's pretty much what we've done using excel find and replace. Thanks again.
June 5, 2008 at 8:44 am
We're all still speculating as to what we think the problem is.
Marcus, how are you capturing the queries from Reporting Services? Can you give us an example of what you are currently doing (your manual process) to capture the query and get it into your table?
There is a way to do this, but are still missing some vital information from you.
June 5, 2008 at 9:04 am
Hi John, unfortunately when we began using reporting services for sql we went ahead without documenting our reports as we created them, now 60 reports later we want to start migrating our database to a new server and are trying to document what each report does. We are going into each report we've created in reporting services through Visual Studio (BIDS) manually and logging datasources and queryies into a table in sql in order to have a record of what each report actually does. We are copying the dataset from the reports and are trying to paste them into a table cell (varchar(max)). But running into syntax problems when we paste into the table due to the single quotes that are embedded into the query statements. My question was if I could write an insert statement that I could paste the query into and somehow format the query so that single quotes that are part of the query would be inserted into the cell without being parsed as part of the insert statement.
ie:
insert into table1(SQLQuery) values('select * from table1 where col1 = 'somevalue')')
Some queries can be dozens of lines long with line breaks and mulitple single quotes. I thought that maybe I could use a replace function in the values portion of my insert statement
ie:
insert into table1(SQLQuery) values(replace('select * from table1 where col1 = 'somevalue', ', '''')')
and just paste each query into the insert statement but I couldn't figure it out using sql and had to use a work around. If you have any other suggestions on how I could go about doing this it would be appreciated.
Thank you.
June 5, 2008 at 9:48 am
OK, that makes sense, you're capturing the queries manually through Visual Studios. In that case, you'll have to do as previously suggested and first past it into a text editor and do a find/replace with the single quotes. Thanks for posting more info. It was just hard to tell what you were really doing. I was not sure if you were trying to capture the queries with profiler, manually, or some other method.
One thing you could do is run a profiler trace and then run through your reports. Once the trace is complete, have it save to a table. Then just do an INSERT INTO WHERE EventClass = 41.
June 5, 2008 at 10:12 am
Thanks for the dilligence John, I'll explore your suggestion.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply