May 13, 2008 at 5:38 am
I have one doubt!!!
I create a stored procedure, in that i am executing the sql statement. I declare variable @v_sql.
SET @v_sql = 'SELECT a.[appointmentslot_id], a.[slot_type], u.[display_name] as slot_type_desc, '+
'a.[start_time], a.[location_id], l.[location_name], l.[domain_id], a.[effective_from], '+
'a.[effective_to], a.[recurring], a.[recurrence_type], a.[recurrence_interval], '+
'a.[dayofweekmask], a.[number_of_exceptions], a.[timeless_slot] '+
'FROM dbo.echtb_appointmentslot a, dbo.echtb_location l, dbo.echtb_appointment_type u '+
'WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id '+
'AND a.effective_from <= '+'"'+convert(varchar(40),@p_end_date)+'"'+
' AND a.effective_to >= '+'"'+convert(varchar(40),@p_start_date)+'"';
and executed this sp. when i am calling this stored procedure i am getting the error:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'recurrence_ty'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'recurrence_ty'.
please reply to this query asap.
thanks in advance....
May 13, 2008 at 5:42 am
U cannot use double qoutes.
"Keep Trying"
May 13, 2008 at 3:28 pm
What Chirag said. You have to use single quotes for your string comparisons, so they need to be escaped. The statements should be:
[font="Courier New"]...
+ 'AND a.effective_from <= ''' + convert(varchar(40),@p_end_date) + ''' '
+ 'AND a.effective_to >= ''' + convert(varchar(40),@p_start_date) + ''' ';
[/font]
You should also move your join predicates from the WHERE clause to the FROM clause.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 13, 2008 at 8:53 pm
Hi Chirag!!!
thanks for your reply.....
here find my comments....
SET @v_sql = 'SELECT a.[appointmentslot_id], a.[slot_type], u.[display_name] as slot_type_desc, '+
'a.[start_time], a.[location_id], l.[location_name], l.[domain_id], a.[effective_from], '+
'a.[effective_to], a.[recurring], a.[recurrence_type], a.[recurrence_interval], '+
'a.[dayofweekmask], a.[number_of_exceptions], a.[timeless_slot] '+
'FROM dbo.echtb_appointmentslot a, dbo.echtb_location l, dbo.echtb_appointment_type u '+
'WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id ';
even for this string also i am getting the same error. If i have taken String
SET @v_sql = 'SELECT a.[appointmentslot_id], a.[slot_type], u.[display_name] as slot_type_desc, '+
'a.[start_time], a.[location_id], l.[location_name], l.[domain_id], a.[effective_from] '+
'FROM dbo.echtb_appointmentslot a, dbo.echtb_location l'+
'WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id ';
it is executing.... when i have taken a long query (String) ... i am getting the error : Unclosed quotation mark after the character string 'recurrence_ty'.
I think this error is coming coz of long string.... wehn i am taking the query with less fields i am able to execute but when the query fields are long i am getting the error. But i need to use more fields in the query.
Please ....lemme know ... thanks in advance.
May 14, 2008 at 12:23 am
whats the datatype and length of @v_sql?
May 14, 2008 at 12:30 am
yes check the datatype & length of the variable - @v_sql.
"Keep Trying"
May 14, 2008 at 7:18 am
i did try this
declare @v_sql varchar(8000)
SET @v_sql = 'SELECT a.[appointmentslot_id], a.[slot_type], u.[display_name] as slot_type_desc,
a.[start_time], a.[location_id], l.[location_name], l.[domain_id], a.[effective_from],
a.[effective_to], a.[recurring], a.[recurrence_type], a.[recurrence_interval],
a.[dayofweekmask], a.[number_of_exceptions], a.[timeless_slot]
FROM dbo.echtb_appointmentslot a, dbo.echtb_location l, dbo.echtb_appointment_type u
WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id ';
SELECT @v_sql
on SQL 2000 it didnt fit into the parameter, and i did try it on SQL 2005 it runs just fine?
..>>..
MobashA
May 14, 2008 at 10:23 pm
Hi all.... thanks for your reply...
i am executing this quey in the stored procedure...
i have taken like DECLARE @v_sql VARCHAR(8000);
when i am trying to set the string as
SET @v_sql = 'SELECT a.appointmentslot_id, a.slot_type, u.display_name AS slot_type_desc, '+
'a.start_time, a.location_id, l.location_name, l.domain_id, a.effective_from, '+ 'a.effective_to, a.recurring, a.recurrence_type, a.recurrence_interval, '+ 'a.dayofweekmask, a.number_of_exceptions, a.timeless_slot '+ 'FROM dbo.echtb_appointmentslot a, dbo.echtb_location l, dbo.echtb_appointment_type u '+
'WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id '+
'AND a.effective_from <= '+'"'+convert(varchar(40),@p_end_date)+'"'+
' AND a.effective_to >= '+'"'+convert(varchar(40),@p_start_date)+'"';
i am getting the error "The multi-part identifier 'a.appointmentslot_id' could not be bound."
when i am trying to use the [] for the columns
SET @v_sql = 'SELECT a.[appointmentslot_id], a.[slot_type], u.[display_name] AS slot_type_desc, '+
'a.[start_time], a.[location_id], l.[location_name], l.[domain_id], a.[effective_from], '+
'a.[effective_to], a.[recurring], a.[recurrence_type], a.[recurrence_interval], '+
'a.[dayofweekmask], a.[number_of_exceptions], a.[timeless_slot] '+
'FROM dbo.echtb_appointmentslot a, dbo.echtb_location l, dbo.echtb_appointment_type u '+
'WHERE a.location_id = l.location_id AND a.slot_type = u.appointment_type_id '+
'AND a.effective_from <= '+'"'+convert(varchar(40),@p_end_date)+'"'+
' AND a.effective_to >= '+'"'+convert(varchar(40),@p_start_date)+'"';
i am getting the error "Unclosed quotation mark after the character string 'recurrence_ty'."
i tried to use the varchar (max) also.... BUT i am getting the same error....
when i am executing the query from the query analyzer its working fine. but whn i tried from the stored procedure i am getting this error.
please lemme know.... thanks in advance.
May 14, 2008 at 10:44 pm
Do you try uncheck the 'Set quoted_identifier' in your SQL query analyzer->options->Connection properties???
hope can help
May 14, 2008 at 10:49 pm
Looking at what you are providing, there really is not too much we can do to identify the problem. Try just selecting the variable instead of executing it and then you can see how the query is formed.
BTW - I see absolutely nothing in this query that would require dynamic SQL. Why are you using dynamic SQL for this?
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
May 14, 2008 at 10:58 pm
i set the QUOTED_IDENTIFIER OFF and i tried... but no use...
i removed the recurrence_type and recurrence_interval from the above query then it is executing. but i need these two columns. is there any way like select column(2) to get that column values?
May 15, 2008 at 1:09 am
Hi All... thanks for your reply....
finally i got it.... i did not do any changes... i removed some columns and added once again in the query... its working fine.....
now i have declared variable varachar(8000) and it is working fine....
thanks for you all for your esteemed support.
- Rams
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply