Report either restarts or closes VS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Report either restarts or closes VS

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2013-05-24 : 13:58:31

Visual Studios either restarts or closes when I try to run a report.

Background:
Database is SQL Server 2008 R2
Report is through Visual Studios 2008

I have a very simple stored procedure that has two parameters and a simple select statement. The .sql looks like this:


/* Create Stored Procedure */
CREATE PROCEDURE dbo.mySPROC
	@tabName varchar(50) = NULL
	, @mnemonic varchar(15) = NULL
AS

/****
Temp table to process a list of @tabName parameters using a user defined function 
****/
create table  #tabName(string varchar(50))
insert #tabName(string)
select string 
    from dbo.CSVToVarChar(@tabName, ',')

/****
Temp table to process a list of @mnemonic parameters using a user defined function 
****/
create table  #mnemonic(string varchar(15))
insert #mnemonic(string)
select string
    from dbo.CSVToVarChar(@mnemonic, ',')

SELECT 
	dstu.dbName AS DBName
	, dstu.dict_token AS Token
	, dstu.colname AS ColumnName
	, dstu.tabname AS TableName
	, dstu.mnemonic AS mnemonic
	, dstu.pfdesc AS PF_Descrip
	, dstu.batch_update AS Batch_Update
	, dstu.list_sequence AS List_Sequence
	, dstu.cross_year AS Cross_Year
FROM 
	dbo.dict_stu dstu 
WHERE 
	dstu.tabName LIKE 'User_%' 
AND	dstu.tabname IN (select string from #tabName)
OR	(dstu.tabname IN
		( CASE WHEN ISNULL(@tabName,dstu.tabname) = 'ALL'
			THEN dstu.tabname ELSE @tabName
			END
		)
	 )

AND	dstu.mnemonic in (select string from #mnemonic)

ORDER BY
	dstu.tabname
	, dstu.dict_token
GO

The .rdl has three datasets:
1) dsCustomData (from the SPROC)
2) dsTableName (for the tabname parameter)
3) dsMnemonic (for the mnemonic parameter)

The parameters are set to allow multiple values.

Is there something I am doing wrong?

Thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Got some code from Rob. Can anyone help?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-24 : 15:13:37

One easy test you can do is to run the stored procedure in SSMS.

If that succeeds, refresh the dataset; that should again go to the database to get the result set schema and thus cause the stored proc to run.

If that succeeds, hard-code the parameters and try to run again.

I have read reports/complaints/forums that when you use temp tables in stored procedures that are called by SSRS, there can be problems. I have never seen any evidence of that though. You can

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2013-05-24 : 17:17:15

Thanks James.

I declared the two parameters and ran the temp tables. Then ran the rest of the sproc and the SPROC didn't pull any records in SSMS.

Can you see anything that would prevent it from doing so?

/****
Temp table to process a list of @tabName parameters using a user defined function 
****/
DECLARE @tabName varchar(50) = NULL
create table  #tabNameTblVar(string varchar(50))
insert #tabNameTblVar(string)
select string 
    from dbo.JHU_CSVToVarChar(@tabName, ',')
GO

/****
Temp table to process a list of @mnemonic parameters using a user defined function 
****/
DECLARE @mnemonic varchar(15) = NULL
create table  #mnemonicTblVar(string varchar(15))
insert #mnemonicTblVar(string)
select string
    from dbo.JHU_CSVToVarChar(@mnemonic, ',')
GO

SELECT 
	dstu.dbName AS DBName
	, dstu.dict_token AS Token
	, dstu.colname AS ColumnName
	, dstu.tabname AS TableName
	, dstu.mnemonic AS mnemonic
	, dstu.pfdesc AS PF_Descrip
	, dstu.batch_update AS Batch_Update
	, dstu.list_sequence AS List_Sequence
	, dstu.cross_year AS Cross_Year
FROM 
	JHU_PF_ALL_V_dict_stu dstu 
WHERE 
	dstu.tabName LIKE 'User_%' -- custom data begins with 'User_'; this accounts for future additions
AND	dstu.tabname IN (select string from #tabNameTblVar) 
AND	dstu.mnemonic in (select string from #mnemonicTblVar
			-- where tabName = @tabName
			)

I had to comment out the "where tabName = @tabName" because it kept giving me an error.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Got some code from Rob. Can anyone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources