SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Report either restarts or closes VS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ledell
SQL NASCAR Parsing Chick

USA
107 Posts

Posted - 05/24/2013 :  13:58:31  Show Profile  Visit Ledell's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/24/2013 :  15:13:37  Show Profile  Reply with Quote

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


Edited by - James K on 05/24/2013 15:14:31
Go to Top of Page

Ledell
SQL NASCAR Parsing Chick

USA
107 Posts

Posted - 05/24/2013 :  17:17:15  Show Profile  Visit Ledell's Homepage  Reply with Quote
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?

Edited by - Ledell on 05/24/2013 18:06:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000