February 16, 2015 at 10:01 am
I have a report which basically lists all of the databases I have on my SQL estate.
I have a parameter which, if I type the correct database name in, it will bring back all of the information about this database so I know where it lives.
I want to be able to search for part names of databases. So where database name like ParamInput%
How is this possible?
February 16, 2015 at 10:07 am
SQLAssAS (2/16/2015)
I have a report which basically lists all of the databases I have on my SQL estate.I have a parameter which, if I type the correct database name in, it will bring back all of the information about this database so I know where it lives.
I want to be able to search for part names of databases. So where database name like ParamInput%
How is this possible?
There is nowhere near enough information to provide a detailed answer here. The basic gist of this is that you will have to use dynamic sql. Be VERY careful here because you have the possibility of sql injection.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2015 at 10:13 am
Hi..
I have 5 columns in my report:
ServerInstance, Database Name, Recovery Model,
Compatibility, State
This has all the databases across our SQL estate in it.
Its main function is to enable me and other to search and see where a specific database is sitting.
If we don't know the full name of the database but know of the project it belongs to etc.. we want to be able to search for part of its name using a wild card. I Could do this in the back end using a wild card, but i want to enable others to do this via SSRS.
Hope that gives more info
February 16, 2015 at 10:15 am
SQLAssAS (2/16/2015)
Hi..I have 5 columns in my report:
ServerInstance, Database Name, Recovery Model,
Compatibility, State
This has all the databases across our SQL estate in it.
Its main function is to enable me and other to search and see where a specific database is sitting.
If we don't know the full name of the database but know of the project it belongs to etc.. we want to be able to search for part of its name using a wild card. I Could do this in the back end using a wild card, but i want to enable others to do this via SSRS.
Hope that gives more info
Why not have a dropdown with the database names in it? That would eliminate the need to lookup the database using a wildcard search.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2015 at 10:23 am
I know how to do a drop down, but want to know how to do a wildcard search too 😉
February 16, 2015 at 10:31 am
SQLAssAS (2/16/2015)
I know how to do a drop down, but want to know how to do a wildcard search too 😉
https://msdn.microsoft.com/en-us/library/ms179859.aspx
As I said before you are going to have to be careful with sql injection if you go down this path.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2015 at 3:36 am
Thanks for your help so far
So i created a procedure:
alter procedure sp_SSRSFindDatabase @DatabaseName varchar(30)
as
select RTRIM(@Databasename) --+ '%'
select [ServerInstance], [Database Name],[Owner], [Recovery Model] , [Compatibility Level], [State]
from [dbo].[tbl_Databases]
where [Database Name] like '%' + @DatabaseName + '%'
go
When i manually run this and use it, it works.
Now im trying to get it into SSRS
1) Create data set which uses this stored procedure, this creates the parameter too
2) add the param to my main data set
I try to run it, and it returns no results
Any idea what im missing?
February 17, 2015 at 4:13 am
I am now getting the error
Incorrect syntax near 'nvarchar' - i have extended the variable to 100 to see if it was too short and it still does not work
February 17, 2015 at 6:35 am
Add a filter to the dataset
Set the expression to
=Left(Fields!DatabaseName.Value,Len(Parameters!ReportParameter1.Value))=Parameters!ReportParameter1.Value
Change the expression return to Boolean
Leave operator as =
Set Value to true
Far away is close at hand in the images of elsewhere.
Anon.
February 17, 2015 at 6:45 am
Hi David, thanks for your reply
the same error still happens im afraid!
I put this filter on the main data set, then tried the second data set but the error happened on both. Is there any more info i can provide?
February 17, 2015 at 6:58 am
What second dataset?
Do you not have one parameter and one dataset (to retrieve all data for all databases) and currently filtering the dataset with the parameter?
Far away is close at hand in the images of elsewhere.
Anon.
February 17, 2015 at 7:02 am
thats the best thing you could have said "what second dataset"
I was using 2 datasets, one for the main data set which had all of the information in the table, then a second with the stored proc
i have removed the first one, so nows there is just one data set using the stored proc. And its working.
SSRS Newbie at work! Thanks for your help
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply