Incorrect syntax near comparison on parameter value

Error: Incorrect syntax near '@.today'.

DECLARE @.today CHAR(8)

SET @.today = CONVERT(CHAR(8), GETDATE(), 112)

SELECT c.name,

c.cust,

m.num,

ph.Entered

FROM Master m (NOLOCK)

INNER JOIN dbo.hisy ph ON ph.number = m.num

INNER JOIN dbo.Cust c ON c.Cust = m.Cust

WHERE m.customer IN (0000162, 0000164)

AND ph.Entered IS NOT NULL

AND CONVERT(CHAR(8), ph.Entered, 112) = @.today <-Error is here

It passes syntax checking for me. Is this the entire batch?

If I add a BEGIN before the statement, I get the syntax error two. Sometimes SQL Server error messages are really misleading, though it is technically correct.

|||thanks I was missign the END, that was it.

Incorrect syntax near ',' using a Multi-value parameter

I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','I fixed it by changing my '=' signs to 'IN', so that it would search for the multi-value parameter within the values selected.

Incorrect parameter order - Months

Hi to everyone,
Sorry if this has been asked before but I couldn't see anything similar from a search of the forums.
I'm trying to filter reports using two parameters set up in the data tab of report builder in VS 2005. I want to filter on month and year, and when I set these filters up in the data tab VS creates the two report parameters for me. When the report is previewed or deployed, the year parameter drop-down list is in the correct order, but the month parameter drop-down list is in alphabetical order (April, August, December, etc).
I have set the time dimension up, with the hierarchy year>month>date. Is there any way to force the months into their correct order? I have created parameters of my own to do this, but the ability to select more than one item with the VS-generated parameters would really enhance the reports I am building.
If anyone can help or post links to resources that'd be great - Many thanks.

If you don't mind having all months appear all the time, you can open the report in VS Report Designer and change the parameter's ValidValues property from a query to a static list of values in the correct order. The report may not re-open in Report Builder afterwards, or if it does, the ValidValues property will be reset to a query, so be aware of that.

Incorrect parameter order - Months

Hi to everyone,
Sorry if this has been asked before but I couldn't see anything similar from a search of the forums.
I'm trying to filter reports using two parameters set up in the data tab of report builder in VS 2005. I want to filter on month and year, and when I set these filters up in the data tab VS creates the two report parameters for me. When the report is previewed or deployed, the year parameter drop-down list is in the correct order, but the month parameter drop-down list is in alphabetical order (April, August, December, etc).
I have set the time dimension up, with the hierarchy year>month>date. Is there any way to force the months into their correct order? I have created parameters of my own to do this, but the ability to select more than one item with the VS-generated parameters would really enhance the reports I am building.
If anyone can help or post links to resources that'd be great - Many thanks.

If you don't mind having all months appear all the time, you can open the report in VS Report Designer and change the parameter's ValidValues property from a query to a static list of values in the correct order. The report may not re-open in Report Builder afterwards, or if it does, the ValidValues property will be reset to a query, so be aware of that.

Incorrect Parameter in Desing Mode

WHERE (Cono = @.Company) AND (DATEPART(month, PaymentDate) = @.Month) AND (DATEPART(year, PaymentDate) = @.Year)

If I run the job in preview mode I enter the parameter data as requested and it runs correctly. When I go back to design mode and run the query using the ! (the parameter box pops up with the data I entered in preview mode) I get an error message - The Parameter is incorrect.

I've tried setting the parameters to every combination of string/interger I can think of.

What is happening here?

Try to eliminate parameters one by one (replace with a literal value), so you'll know which parameter causes the error message.|||It has to do with the cono (Company) parameter. Cono is defined as integer. I get the message no matter if I set the parameter value to string or integer.

Including Report Parameters in SQL Query

I have created a DAtaset for using in my report. Everything works. My SQL
contains Top 4 ... which I want to pass as a parameter.
I created a report parameter called "Months". How can I replace top 4 by top
(parameters!months.value) '
However I try to include the parameter in my SQL query it fails to work.I think in this case you need to use an expression for the source. Use the
generic query designer and do this:
= "some sql statements blah blah Top " & parameters!Months.value & " rest of
SQL Statement"
Remember that when refering to a parameter it is case sensitive so it much
match exactly.
When I do this I will sometimes first work out the expression using a report
with a single textbox and my parameters and set the textbox to the
expression so I can see it prior to using it for the source of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>I have created a DAtaset for using in my report. Everything works. My SQL
> contains Top 4 ... which I want to pass as a parameter.
> I created a report parameter called "Months". How can I replace top 4 by
> top
> (parameters!months.value) '
> However I try to include the parameter in my SQL query it fails to work.|||Thanks fror your reply.
I have tried that. What I don't understand is where should I stop the SQL
String with " ? I have created the query in VS 2003 on the data tab with the
grafical query creator (looking like SQL Enterprise manager).
There is no SQL String inside quotation marks I could interrupt, enter the
parameter and continue the sql string with quotation marks. Do you get my
problem?
markus
"Bruce L-C [MVP]" wrote:
> I think in this case you need to use an expression for the source. Use the
> generic query designer and do this:
> = "some sql statements blah blah Top " & parameters!Months.value & " rest of
> SQL Statement"
> Remember that when refering to a parameter it is case sensitive so it much
> match exactly.
> When I do this I will sometimes first work out the expression using a report
> with a single textbox and my parameters and set the textbox to the
> expression so I can see it prior to using it for the source of the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >I have created a DAtaset for using in my report. Everything works. My SQL
> > contains Top 4 ... which I want to pass as a parameter.
> >
> > I created a report parameter called "Months". How can I replace top 4 by
> > top
> > (parameters!months.value) '
> >
> > However I try to include the parameter in my SQL query it fails to work.
>
>|||The issue is that you are in the graphical query instead of the generic
query designer. There is a button (hover over them) to switch to the other
designer. Then put an = sign in from of it.
For instance, let's say that your query looks like this:
select * from sometable
Click on the button (to the right of the ...). Now do this:
= "select * from sometable"
It should work for you. Now modify it to include your parameter as I had it
below.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> Thanks fror your reply.
> I have tried that. What I don't understand is where should I stop the SQL
> String with " ? I have created the query in VS 2003 on the data tab with
> the
> grafical query creator (looking like SQL Enterprise manager).
> There is no SQL String inside quotation marks I could interrupt, enter the
> parameter and continue the sql string with quotation marks. Do you get my
> problem?
> markus
> "Bruce L-C [MVP]" wrote:
>> I think in this case you need to use an expression for the source. Use
>> the
>> generic query designer and do this:
>> = "some sql statements blah blah Top " & parameters!Months.value & " rest
>> of
>> SQL Statement"
>> Remember that when refering to a parameter it is case sensitive so it
>> much
>> match exactly.
>> When I do this I will sometimes first work out the expression using a
>> report
>> with a single textbox and my parameters and set the textbox to the
>> expression so I can see it prior to using it for the source of the
>> dataset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
>> message
>> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>> >I have created a DAtaset for using in my report. Everything works. My
>> >SQL
>> > contains Top 4 ... which I want to pass as a parameter.
>> >
>> > I created a report parameter called "Months". How can I replace top 4
>> > by
>> > top
>> > (parameters!months.value) '
>> >
>> > However I try to include the parameter in my SQL query it fails to
>> > work.
>>|||Ok, I found it. I changed my SQL to:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM dbo.v_Calls_Statistik_Sum
ORDER BY Jahr DESC, Monat DESC"
But after editing my SQL I get an error:
"Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler: [BC30648]
Zeichenfolgenliterale müssen mit einem doppelten Anführungszeichen enden."
~
"The expression for Object 'DATASETNAME' contains error BC30648. Literals
have to end with double quotation marks."
My Parameter is an integer. Why or where do I have to use quotation marks?
Thank you so much,
markus
"Bruce L-C [MVP]" wrote:
> The issue is that you are in the graphical query instead of the generic
> query designer. There is a button (hover over them) to switch to the other
> designer. Then put an = sign in from of it.
> For instance, let's say that your query looks like this:
> select * from sometable
> Click on the button (to the right of the ...). Now do this:
> = "select * from sometable"
> It should work for you. Now modify it to include your parameter as I had it
> below.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> > Thanks fror your reply.
> >
> > I have tried that. What I don't understand is where should I stop the SQL
> > String with " ? I have created the query in VS 2003 on the data tab with
> > the
> > grafical query creator (looking like SQL Enterprise manager).
> > There is no SQL String inside quotation marks I could interrupt, enter the
> > parameter and continue the sql string with quotation marks. Do you get my
> > problem?
> > markus
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I think in this case you need to use an expression for the source. Use
> >> the
> >> generic query designer and do this:
> >> = "some sql statements blah blah Top " & parameters!Months.value & " rest
> >> of
> >> SQL Statement"
> >>
> >> Remember that when refering to a parameter it is case sensitive so it
> >> much
> >> match exactly.
> >>
> >> When I do this I will sometimes first work out the expression using a
> >> report
> >> with a single textbox and my parameters and set the textbox to the
> >> expression so I can see it prior to using it for the source of the
> >> dataset.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
> >> message
> >> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >> >I have created a DAtaset for using in my report. Everything works. My
> >> >SQL
> >> > contains Top 4 ... which I want to pass as a parameter.
> >> >
> >> > I created a report parameter called "Months". How can I replace top 4
> >> > by
> >> > top
> >> > (parameters!months.value) '
> >> >
> >> > However I try to include the parameter in my SQL query it fails to
> >> > work.
> >>
> >>
> >>
>
>|||MarkusPoehler wrote:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> But after editing my SQL I get an error:
> "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> Anführungszeichen enden." ~
Markus,
schreib das mal so:
write it in that way:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
Du muss den ganzen String quoten
You have to quote the whole string with "
best regards
Frank
--
www.xax.de|||Frank Matthiesen wrote:
nochmal wg. Umbruch
= "SELECT TOP " +
" & parameters!anzMonate.Value & " +
" dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum " +
" ORDER BY Jahr DESC, Monat DESC"
gruss
frank|||Hi Frank,
Hi Fränk, :)
vielen Dank, das war ein entscheidender Hinweis. Darauf wäre ich nie
gekommen, dass dieser Text-SQL Editor plötzlich Zeilen unterscheidet.
Thank you! This was the crux.
Markus
"Frank Matthiesen" wrote:
> MarkusPoehler wrote:
> > = "SELECT TOP " & parameters!anzMonate.Value & "
> > dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> > dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> >
> > But after editing my SQL I get an error:
> >
> > "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> > [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> > Anführungszeichen enden." ~
>
> Markus,
> schreib das mal so:
> write it in that way:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, " +
> " Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> Du muss den ganzen String quoten
> You have to quote the whole string with "
> best regards
> Frank
> --
> www.xax.de
>
>
>

Including multivalued parameter in URL

I know how to include a parameter in a URL, by appending it after & sign at
end of report URL - works fine.
I can not figure out the syntax (if it is in fact possible) of passing a
multiple selection for a multivalued parameter. I tried both these formats,
where the ... represents the entire URL of the base report, including the
render command:
1) ...&Comp_Inv_ID=C123, C345
2) ...&Comp_Inv_ID=C123%2c%20C345 (i.e., converting the comma and space to
the %hex format, where hex 2c is comma and hex 20 is space)
Note that ...&Comp_Inv_ID=C123 works, selecting the single item.
Is it possible I have to somehow include each value as its own
&Comp_Inv_ID=<val> pair, connected by some sort of AND condition?I know one way to do it, but it isn't real elegant.
rather than this:
¶m=val1,val2,val3
you do this:
¶m=val1¶m=val2¶m=val3
isaksp00 wrote:
> I know how to include a parameter in a URL, by appending it after & sign at
> end of report URL - works fine.
> I can not figure out the syntax (if it is in fact possible) of passing a
> multiple selection for a multivalued parameter. I tried both these formats,
> where the ... represents the entire URL of the base report, including the
> render command:
> 1) ...&Comp_Inv_ID=C123, C345
> 2) ...&Comp_Inv_ID=C123%2c%20C345 (i.e., converting the comma and space to
> the %hex format, where hex 2c is comma and hex 20 is space)
> Note that ...&Comp_Inv_ID=C123 works, selecting the single item.
> Is it possible I have to somehow include each value as its own
> &Comp_Inv_ID=<val> pair, connected by some sort of AND condition?|||Thanks! I had just figured that one out about an hour ago as well.
Decidedly inelegant.
"Potter" wrote:
> I know one way to do it, but it isn't real elegant.
>
> rather than this:
>
> ¶m=val1,val2,val3
>
> you do this:
>
> ¶m=val1¶m=val2¶m=val3
>
> isaksp00 wrote:
> > I know how to include a parameter in a URL, by appending it after & sign at
> > end of report URL - works fine.
> >
> > I can not figure out the syntax (if it is in fact possible) of passing a
> > multiple selection for a multivalued parameter. I tried both these formats,
> > where the ... represents the entire URL of the base report, including the
> > render command:
> >
> > 1) ...&Comp_Inv_ID=C123, C345
> > 2) ...&Comp_Inv_ID=C123%2c%20C345 (i.e., converting the comma and space to
> > the %hex format, where hex 2c is comma and hex 20 is space)
> > Note that ...&Comp_Inv_ID=C123 works, selecting the single item.
> > Is it possible I have to somehow include each value as its own
> > &Comp_Inv_ID=<val> pair, connected by some sort of AND condition?
>