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

Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -