How to validate the parameter values?

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - Error Connecting to Remote Server Using Microsoft SQL Server Management Studio Express I am facing same problem. I want to connect to Remote Server (windows 2003 server). Iam using Deveoper ed. connecting to express ed. I have allowed TCP-IP and enabled browser service. and restarted server also. windows firewall disabled. I want to know where port involes in sql server and where to configure port for both sql server server. Does SP1 creates probelm if it is not installed. Tag: How to validate the parameter values?
  - 2
    - parameterize SSIS package to identify a source file location BOL has some good information about package configurations. If this is for a stand alone package; you may want to use the /set option of the DTexec utility as posted above. Tag: How to validate the parameter values?
  - 3
    - Create Table within an IF statement causes error They do pay attention to the suggestions. Suggestions for SQL Server http://connect.microsoft.com/sqlserver Tag: How to validate the parameter values?
  - 4
    - Strange Behaviour Sort and Merge Join I found the following script on Internet which will help you change the server collation. Worked fine for me. http://blogs.msdn.com/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx Tag: How to validate the parameter values?
  - 5
    - Server Agent Jobs: Script job as create to file... Hey Derek, I thought you might be interested in seeing what I came up with. Basically, I have a stored proc that creates a stored proc for each job under the server agent. From there I can execute the proc to create the cooresponding agent job. Some work remains on the scheduling but the jist of the task is here... use [DevDB] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --Job DECLARE @jobID nvarchar ( 40 ), @stepID nvarchar ( 40 ), @procName varchar ( 255 ), @jobName varchar ( 255 ), @command_job varchar ( 8000 ), @command_step varchar ( 8000 ), @command_end varchar ( 8000 ), @command_sched varchar ( 8000 ) DECLARE @job_name as varchar ( 255 ), @owner_login_name as varchar ( 255 ), @description as varchar ( 255 ), @category_name as varchar ( 255 ), @enabled as nvarchar ( 4 ) DECLARE @notify_level_email as nvarchar ( 4 ), @notify_level_page as nvarchar ( 4 ), @notify_level_netsend as nvarchar ( 4 ) DECLARE @notify_level_eventlog as nvarchar ( 4 ), @delete_level as nvarchar ( 4 ), @start_step_id nvarchar ( 4 ) --Job Steps DECLARE @step_name varchar ( 255 ), @command varchar ( 8000 ), @database_name varchar ( 255 ), @database_user_name varchar ( 255 ), @subsystem nvarchar ( 40 ) DECLARE @cmdexec_success_code nvarchar ( 2 ), @flags nvarchar ( 2 ), @retry_attempts nvarchar ( 2 ), @retry_interval nvarchar ( 2 ), @output_file_name varchar ( 255 ) DECLARE @on_success_step_id nvarchar ( 3 ), @on_success_action nvarchar ( 2 ), @on_fail_step_id nvarchar ( 2 ), @on_fail_action nvarchar ( 2 ) if EXISTS( select * from DevDB . INFORMATION_SCHEMA.TABLES where table_name = 'server_agent_jobs' ) BEGIN DROP TABLE DevDB .. server_agent_jobs End --ignore multi-server jobs at this time select sj . job_id , name , '0' as executed into DevDB .. server_agent_jobs from msdb .. sysjobs sj inner join msdb .. sysjobservers ss on ss . job_id = sj . job_id and ss . server_id = '0' order by sj . job_id WHILE EXISTS ( select job_id from DevDB .. server_agent_jobs where executed = '0' ) BEGIN --get working job_id and stored proc name SELECT top 1 @jobID = job_id , @procName = 'sp_create_server_agent_job_' + name , @jobName = name FROM DevDB .. server_agent_jobs WHERE executed = '0' order by job_id --get job info from sysjobs select @job_name = a . name , @owner_login_name = b . name , @description = a . description , @category_name = c . name , @enabled = a . enabled , @notify_level_email = a . notify_level_email , @notify_level_page = a . notify_level_page , @notify_level_netsend = a . notify_level_netsend , @notify_level_eventlog = a . notify_level_eventlog , @delete_level = a . delete_level , @start_step_id = a . start_step_id FROM msdb .. sysjobs a , master .. syslogins b , msdb .. syscategories c WHERE a . owner_sid = b . sid and a . category_id = c . category_id and job_id = @jobID --get job info from sysjobsteps if EXISTS( select * from DevDB . INFORMATION_SCHEMA.TABLES where table_name = 'server_agent_job_steps' ) BEGIN DROP TABLE DevDB .. server_agent_job_steps End select *, '0' as executed into DevDB .. server_agent_job_steps from msdb .. sysjobsteps sjs where job_id = @jobID order by sjs . step_id if EXISTS( select * from DevDB . INFORMATION_SCHEMA.TABLES where table_name = 'server_agent_job_schedules' ) BEGIN DROP TABLE DevDB .. server_agent_job_schedules End select *, '0' as executed into DevDB .. server_agent_job_schedules from msdb .. sysjobschedules sjs where job_id = @jobID order by sjs . schedule_id --drop proc if it exists before it's re-created if EXISTS( select * from DevDB . INFORMATION_SCHEMA.ROUTINES where routine_name = @procName ) BEGIN exec ( 'DROP PROCEDURE [' + @procName + ']' ) End SET CONCAT_NULL_YIELDS_NULL OFF select @command_job = 'CREATE Procedure [dbo].[' + @procName + ']( @operator varchar(255) ) AS BEGIN SET NOCOUNT OFF DECLARE @ReturnCode nvarchar (40), @jobID nvarchar (40) Begin Transaction --delete job if it already exists (by job name -> @jobName) if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''') begin EXECUTE msdb.dbo.sp_delete_job @job_name = ''' + @jobName + ''' end --start creating the new job if @operator <> '''' begin EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT, @job_name = ''' + @job_name + ''', @owner_login_name = ''' + @owner_login_name + ''', @description = ''' + @description + ''', @category_name = ''' + @category_name + ''', @enabled = ''' + @enabled + ''', @notify_level_email = ''' + @notify_level_email + ''', @notify_level_page = ''' + @notify_level_page + ''', @notify_level_netsend = ''' + @notify_level_netsend + ''', @notify_level_eventlog = ''' + @notify_level_eventlog + ''', @delete_level = ''' + @delete_level + ''', @notify_email_operator_name = @operator IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback end else begin EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT, @job_name = ''' + @job_name + ''', @owner_login_name = ''' + @owner_login_name + ''', @description = ''' + @description + ''', @category_name = ''' + @category_name + ''', @enabled = ''' + @enabled + ''', @notify_level_email = ''' + @notify_level_email + ''', @notify_level_page = ''' + @notify_level_page + ''', @notify_level_netsend = ''' + @notify_level_netsend + ''', @notify_level_eventlog = ''' + @notify_level_eventlog + ''', @delete_level = ''' + @delete_level + ''' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback end --add job steps ' WHILE EXISTS ( select * from DevDB .. server_agent_job_steps where executed = '0' ) BEGIN select top 1 @stepID = step_id from DevDB .. server_agent_job_steps where executed = '0' order by step_id SELECT @step_name = a . step_name , @command = a . command , @database_name = a . database_name , @database_user_name = a . database_user_name , @subsystem = a . subsystem , @cmdexec_success_code = a . cmdexec_success_code , @flags = a . flags , @retry_attempts = a . retry_attempts , @retry_interval = a . retry_interval , @output_file_name = a . output_file_name , @on_success_step_id = a . on_success_step_id , @on_success_action = a . on_success_action , @on_fail_step_id = a . on_fail_step_id , @on_fail_action = a . on_fail_action FROM DevDB .. server_agent_job_steps a where a . step_id = @stepID select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = ''' + @stepID + ''', @step_name = ''' + @step_name + ''', @command = ''' + REPLACE ( @command , '''' , '''''' ) + ''', @database_name = ''' + @database_name + ''', @server = ''' + '' + ''', @database_user_name = ''' + @database_user_name + ''', @subsystem = ''' + @subsystem + ''', @cmdexec_success_code = ''' + @cmdexec_success_code + ''', @flags = ''' + @flags + ''', @retry_attempts = ''' + @retry_attempts + ''', @retry_interval = ''' + @retry_interval + ''', @output_file_name = ''' + @output_file_name + ''', @on_success_step_id = ''' + @on_success_step_id + ''', @on_success_action = ''' + @on_success_action + ''', @on_fail_step_id = ''' + @on_fail_step_id + ''', @on_fail_action = ''' + @on_fail_action + ''' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' update DevDB .. server_agent_job_steps set executed = '1' where step_id = @stepID END --update start step id from sysjobs select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = ''' + @start_step_id + ''' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' select @command_end = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N''(local)'' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' select @command_end = @command_end + ' COMMIT TRANSACTION GOTO THE_END QuitWithRollback: print ''job failed'' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION THE_END: END' --print (@command_job + @command_step + @command_sched + @command_end) SET CONCAT_NULL_YIELDS_NULL ON execute ( @command_job + @command_step + @command_sched + @command_end ) update DevDB .. server_agent_jobs set executed = '1' where DevDB .. server_agent_jobs . job_id = @jobID select @command_step = '' END Tag: How to validate the parameter values?
  - 6
    - comparing two fields I prefer the "outer join" method, since it helps enforce thinking in terms of INNER and OUTER JOINs. select a . block_code from BLKPTS_ADDXY a left outer join allblkzips s on a.block_code = s.block_code and a.zip = s.zip where (s.zip is null) -- you only need to specify one column here, since the JOIN fails if both do not match Dan Tag: How to validate the parameter values?
  - 7
    - Transaction Question When I run the following in Query Analyzer: SET QUOTED_IDENTIFIER OFF -- allows double quotes (") used as wrapper for dynamic sql. DECLARE @E int BEGIN TRAN T1 PRINT 'Start...' EXEC ( " begin tran T2| raiserror( ' inside proc', 16, 11) PRINT STR(@@ERROR) rollback tran T2 PRINT STR(@@ERROR) return " ) SET @E = @@ERROR IF @E <> 0 BEGIN PRINT 'Error ' + LTRIM ( STR ( @E )) + ' out of here...' ROLLBACK TRAN T1 RETURN END PRINT 'Done.' COMMIT TRAN T1 I get the following output and error: Start... Msg 50000, Level 16, State 11, Line 3 inside proc 50000 Msg 6401, Level 16, State 1, Line 5 Cannot roll back T2. No transaction or savepoint of that name was found. 6401 Msg 266, Level 16, State 2, Line 7 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2. Error 266 out of here... Anyone have an idea why I would get the 6401 and 266 error JD Waleska, GA MSBassSingerNO-SPAM@alltel.NO-SPAMnet (just remove the string NO-SPAM) Tag: How to validate the parameter values?
  - 8
    - Cannot connect to 2000 from 2005 via linked server Interesting issue.. I think I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas Here is the error I get: OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". Msg 10061, Level 16, State 1, Line 0 TCP Provider: No connection could be made because the target machine actively refused it. Tag: How to validate the parameter values?
  - 9
    - Cube processes OK but no rows appear in the browser I have a small cube that process ok. The message shows that a few hundred rows processed from the fact table and the appropriate number of dimensions processed. When I look at the cube in the browser I get blank cells no matter what dimension or measure. Any wisdom Tag: How to validate the parameter values?
  - 10
    - Is there Limitations to Importing data to SQL Server Express though it is a full featured free downloadable edition of sql server 2005, SQL Server express has few limitations. It can only have datbases of maximum 4 GB size. See whether u r database is exceeding this limit of not Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx Madhu Tag: How to validate the parameter values?
  - 11
    - Multi-database Multi-server RS currently does not do joins - you will need to push them into SQL query. What you can do in RS report is to create separate datasets querying separate databases and show results in separate report dataregions. But again you will not be able to join data from multiple report datasets inside of report. Tag: How to validate the parameter values?
  - 12
    - DB does not autogrow Check the properties of the database, 'options' tab there is an 'auto shrink' option Tag: How to validate the parameter values?
  - 13
    - Want Text box font to be inherited from list I want to set properties such as font family, font size, etc as high as possble in the report. Is it possible to have textbox fonts inherited from a container like a list, or report Currently it seems I need to set fonts on every textbox. Similarly, when I use report designer to put a textbox on a report, the textbox decides the Font is Normal, Arial, 10pt, Normal. Where is this setting stored Tag: How to validate the parameter values?
  - 14
    - new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software re: new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software From my desktop the report accessing an Oracle 9i db with no problem. I've deployed other reports accesing sql server with no problem. Now I deploy the report and try to access my Oracle source and get: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater On the server in question, I know they've installed the oracle client. I can access the db from sql plus with no problem. As the user that I access ssrs credentials, i remote login and am able to write to oracle home.. C:\oracle\ora10g\NETWORK\ADMIN i've confirmed my database is in tnsnames and it works from sqlplus. Tag: How to validate the parameter values?
  - 15
    - copying tables DTS vs SSIS - speed!!! the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table. p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content. cheers Tag: How to validate the parameter values?
  - 16
    - using historical attributes with SCD transform on a table with a PK My apologies - should have read your post more closely Tag: How to validate the parameter values?
- Next
  - 1
    - How do I go about placing photographs in SQL? I have SQL 2005 Server and Visual Studio 2005 and I am working in vb. I want to create a database which has photographs. Tag: How to validate the parameter values?
  - 2
    - Average of children thanx will look at named calculations. and try it on data to see what i can get. if i get into any trouble will post here....... Tag: How to validate the parameter values?
  - 3
    - Query Time Limit? I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time For example, if the query takes longer than 20 seconds, have it stop executing and raise an error Thanks, -Dave Tag: How to validate the parameter values?
  - 4
    - Personal Web Developer Kit SQL Error You can download a copy of SQL Server 2005 Express from here . Tag: How to validate the parameter values?
  - 5
    - SQL Server - using a cursor to find and sum login/logout times Hi all, New to using cursors and i was after some help. We have a trace running to find instances of certain sp's, these basically tell us when someone logs in and someone logs out (1 row per event). Importing into a table basically gives me process, user, datetime e.g. 'login', user1, 2007-02-15 10:21:35.590 'login', user2, 2007-02-15 10:21:36.100 'logout', user1, 2007-02-15 10:22:45.100 'login', user3, 2007-02-15 10:23:23.100 'logout', user2, 2007-02-15 10:27:54.436 'logout', user3, 2007-02-15 10:30:03.237 What i need to do is to basically get to the point where we have user, total time in system' e.g. user1, 1min 10secs (close!) user2, 6mins 18secs user3, 6mins 40secs any help much appreciated. Thanks, Jon Tag: How to validate the parameter values?
  - 6
    - Query performance problems with join on UDF-based computed column I'll see if I can come up with a simple repro. What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows. BTW, actually querying the column value is quite fast. It's the join that craters the performance. Tag: How to validate the parameter values?
  - 7
    - Pointers to the best documentation on star joins and the optimiser Hi All, the link http://blogs.msdn.com/bi_systems/pages/164502.aspx . now seems not to lead anywhere and I recall some good tips there...I googled it to see if I could find the original content but no luck... Does anyone know why this link now says 'the forum you requested does not exist' And know where this post went to Thanks in Advance Peter Tag: How to validate the parameter values?
  - 8
    - Multiple Select statements + Stored Procedure May be something like this... Select 'Table 1' as Source, * From FirstTable Union ALL Select 'Table 2' as Source, * From SecondTable Order By Source, .... Tag: How to validate the parameter values?
  - 9
    - hidden views thanks for ur help! myra Tag: How to validate the parameter values?
  - 10
    - Login Failed for <domain name>\<Machine Name> For some reason , the report server is trying to login as <domainname>\<machine name> - its supposed to be <domain>\<user name> or ASPNET or Network Service Authority - I have no clue why it is pickin gup machine name. Any ideas and how to fix Tag: How to validate the parameter values?
  - 11
    - adding client server aliases How can I import a client alias list in SQL Server 2005 Configuration Manager The tool provides a way to export a list, but not to import a list. Adding 100 SQL Server instances via the GUI is time consuming and agonizing. Thanks, Justin Randall Tag: How to validate the parameter values?
  - 12
    - Profiler trace bind variables Thanks a lot for your valuable comments, I believe I got the answers. Thanks again Tag: How to validate the parameter values?
  - 13
    - aspnet_wp.exe crashing intermittently I have an application which is providing printing facility using a custom hardware interface device. Whenever a print is required user punches in his ID number into the hardware device, and hence an underlying application gets the event and it calls a managed DLL, which inturn calls the Reporting services webservice on the same server. Problem starts after some days (typically after 2 -3 days), when the server response becomes slow and the aspnet_wp.exe restarts every now and then and after this sequence of happening, finally it crashes with some event in application in event log which reads: The server was unable to allocate from the system nonpaged pool because the pool was empty. Also many times the applications log file reads : There is an error in XML document (1, 948). I think this error starts when the aspnet_wp.exe is restarting, but I am not sure. The server operating system is Windows 2000 SP-4, MS SQL Server 2000 Standard Edition SP-4, MS Reporting Services 2000 Standard SP-2 with .Net Framework v1.1 The Server is running many applications apart from the ones listed above and there is a moderate usage of TCP/IP sockets for communicating with certain hardware devices. Please help me out! Tag: How to validate the parameter values?
  - 14
    - Problem for Calling A Stored Procedure, Please help. Thanks Hunt: 1st, there are something wrong for this part below: ' Declare tmpcur cursor for ' ' select @a = a FROM' please advise me that how to modify it, coz this is my 1st time to see write cursor this way :) and for my case, i don't really need to use cursor, coz the ' select @A = A FROM' + @table + ' WHERE LEFT(code, 1) = ' + LEFT(@incomingcode, 1) + ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @incomingcode+ ') = 1' + ' ORDER BY LEN(code) DESC' will only back me one set of data. anyway. based on your code, i modified mine as below: alter proc [MaxTime] (@number varchar(30), @numbera varchar(30), @numberb varchar(30), @Total float output, @balance float output) as begin declare @table varchar(20) declare @freetotal varchar(20) declare @SQL nvarchar(4000) select @balance = balance, @table = table, @freetotal = freetotal from info where number = @number SELECT @SQL = \'select @A = A FROM\' + @table + \' WHERE LEFT(code, 1) = \' + LEFT(@incomingcode, 1) + \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @incomingcode+ \') = 1\' + \' ORDER BY LEN(code) DESC\' exec sp_executesql --don't know where did i get those \ from set @Total = @balance / @A + @freetotal end return @Total return @balance however, got error message like: Msg 201, Level 16, State 4, Procedure MaxTime , Line 0 Procedure ' MaxTime ' expects parameter '@Total', which was not supplied. but one step closed i think Cheers. Tag: How to validate the parameter values?
  - 15
    - Data Type Range Hi, When I set a tinyint field as identity, it automatically sets the identity seed as 1. I wonder whether the increment is set to go around in loop. Say, I have the range of 256 numbers for a tinybit. So, an identifier should go from 1 to 255 and then to 0 and then back to 1. Is that right Tag: How to validate the parameter values?
  - 16
    - A CLR SVF works on my DB instance but not on the other one Check the Windows settings. Niels Tag: How to validate the parameter values?

raj_vasant

Hi,

Is there any way to validate the input paratemers for the report For example: I want to restrict the value to be less than 100 in one parameter. How to achieve this

Thnx in advance.

Top

Re: How to validate the parameter values?

Shyam Sundar

There is no way to validate and inform the user if the parameter value is not in a given range. Though you can restrict the values that user can select for the parameter by providing pre-defined available values to the parameter, either from query or non-queried.

Shyam

Top

Re: How to validate the parameter values?

raj_vasant

Pre-defining the values will be helpful in case of numeric or string values. What if i want to compare date values. For example:

given date is less than or equal to today's date... I think, this is also not possible!!!

The RS Engine validates the date based on the format only. The validations are not possible.

Some alternative should be thought-of by microsoft... as validations are very necessary.

Top