Help needed with this sproc

Hi, I am trying to Implement Multi parameter...

If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.

This is my sproc

ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]@.ClientIdnvarchar(max)=NULL,@.StartDatedatetime,@.EndDatedatetimeASDeclare @.SQLTEXT nvarchar(max)If @.ClientIdISNULLBeginSelect o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInnerJoin ClientPlan cpon o.PlanId = cp.PlanIdInnerJoin ClientUser con o.CreatedByUserId = c.UserIdWHERE--cp.ClientId = @.ClientId--AND o.OrderDateBETWEEN @.StartDateAND @.EndDateORDER BYo.OrderIdDESCENDELSEBEGIN SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' + @.ClientId +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'execute (@.SQLTEXT)END

any help will be appreciated.

Regards

Karen

Try this:

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'Exec(@.SQLTEXT)

If you still have errors, post exactly how you are calling the proc.

|||

i am first execting the stored proc using query analyser and this is how i am calling it

usp_GetOrdersByOrderDate'7,4','12/17/2007','12/20/2007'

|||

ahh.. you need quotes around the dates too..

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ''' +Convert(varchar,@.StartDate) +''' AND ''' +convert(varchar, @.EndDate) +''' ORDER BYo.OrderId DESC'

|||

thanks,,,

Yes that did it.

Regards

Karen

Help needed on Full text Search

Hello,
we got a requirement where i hvae to implement a full text search in SQL
Server 2000/2005. Can some one help me Where/How to start ? Any links for
the articles is also good.
Thanks in Advance
Srinivas
have a look at the links in indexserverfaq.com for sql fts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srinivas Kollipara" <skollipara@.stratasolutions.com> wrote in message
news:eweiaU9FGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello,
> we got a requirement where i hvae to implement a full text search in SQL
> Server 2000/2005. Can some one help me Where/How to start ? Any links for
> the articles is also good.
> Thanks in Advance
> Srinivas
>

Help needed on Full text Search

Hello,
we got a requirement where i hvae to implement a full text search in SQL
Server 2000/2005. Can some one help me Where/How to start ? Any links for
the articles is also good.
Thanks in Advance
Srinivashave a look at the links in indexserverfaq.com for sql fts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srinivas Kollipara" <skollipara@.stratasolutions.com> wrote in message
news:eweiaU9FGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello,
> we got a requirement where i hvae to implement a full text search in SQL
> Server 2000/2005. Can some one help me Where/How to start ? Any links for
> the articles is also good.
> Thanks in Advance
> Srinivas
>

Help needed on Full text Search

Hello,
we got a requirement where i hvae to implement a full text search in SQL
Server 2000/2005. Can some one help me Where/How to start ? Any links for
the articles is also good.
Thanks in Advance
Srinivashave a look at the links in indexserverfaq.com for sql fts.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srinivas Kollipara" <skollipara@.stratasolutions.com> wrote in message
news:eweiaU9FGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello,
> we got a requirement where i hvae to implement a full text search in SQL
> Server 2000/2005. Can some one help me Where/How to start ? Any links for
> the articles is also good.
> Thanks in Advance
> Srinivas
>

Help needed in SQL Query Search!

Hi everyone,

I'm trying to implement SQL Server database search. The details are:-
1. I have table called EMPLOYEE has FNAME,LNAME etc cols.
2. User might look for any employee using either FNAME or LNAME
3. I have search box in asp.net where user could enter search string
The sample data:
FNAME LNAME
abc george
def george
rkis lita
rose lita

The query i wrote:
SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'
My problem is:-
1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.
Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.

When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.

Is this possible anyway in T-SQL or Fulltext search.

Your help is greatly appreciated.

Thanks
Bob

Bob,
You were on the right track with Soundex, here's how you can use it. Also look into difference.
Sample data: (pubs)

au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

648-92-1872 Blotchet-Halls Reginald

238-95-7766 Carson Cheryl

722-51-5454 DeFrance Michel

712-45-1867 del Castillo Innes

427-17-2319 Dull Ann

213-46-8915 Green Marjorie

527-72-3246 Greene Morningstar

472-27-2349 Gringlesby Burt

My Queries:

select*from authorswheresoundex(au_lname)=soundex('Benet')

Returns:
au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

select au_id,au_lname,au_fnamefrom authorswheredifference(au_lname,'whit')> 3

Returns:
au_id au_lname au_fname

---- ------------ -------

172-32-1176 White Johnson

Hope this helps, as you can see here it is definitely possible to get the "similar" functionality. Check books online for more info on difference, iirc it returns one of a number of different values that state how close or different the words are.
Scott

Help needed for SSRS 2005(Regarding Individual folder Security)

Hi Experts,
I am working on SSRS 2005.
I want to implement security for each folder for different users on my
report server.
I have 6 different folders on my report server and I want that any
person can open them after entering a valid username and password. I
want set different username and password for each folder not a single
password for all folders.
I am not finding anyway to do this. So if anybody have any idea about
this please tell me.
Any help will be appreciated.
Regards
DineshOn May 3, 4:06 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
Normally, you would tie the folder-level security into Windows
authentication, based on user and group access (via select a folder ->
select the Properties tab -> select Security on the left-hand side ->
Edit item security on the top). You might make a new role w/only
certain members and that role only has access to certain folders;
however, again this is windows authentication based. The only other
way to go about it would be to create a parameter in each report that
accepts text input and use this as a password. Sorry that I could not
be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||As Enrique mentioned you can't do this.
This is not how Windows works. Consider this the same as if you had a file
server and were trying to do this. You could not do it.
Same result is to do the following. Create 6 local groups. To the local
groups add the users (you can add either individual domain users or domain
groups to the local groups). Then use these groups when setting up roles and
access to the folders.
You should read up on roles and security in RS.
One other possibility is to implement forms security. This is where you
integrate your own authentication mechanism.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1178183193.254090.33670@.c35g2000hsg.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
>