April 16, 2008 at 12:21 pm
Hi,
will u plz tell me i am new in ssrs but i am trying to work on it but when i try this query to run it show me this error
CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Incorrect syntax near '?'.
------------------------------
ADDITIONAL INFORMATION:
Incorrect syntax near '?'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
so plz tell me what can i do but before clicking run 1 bar comes out it asked for null value and blank at space of ? but i dont know what to do plz tell me
thaxxx
April 16, 2008 at 3:13 pm
I'm not sure what's going on in your case, but you might have trouble with the temp table "#temp".. why not try a table variable:
create procedure dbo.GetTableSpaceUsed
as
begin
SET NOCOUNT ON
declare @Space table (table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
INSERT @Space
EXEC sp_msforeachtable 'sp_spaceused ''?'''
select *
from @Space
end
April 16, 2008 at 4:07 pm
Tung,
You cannot insert into a table variable via stored proc call...
Jag,
In SSRS data sets question marks can be thought of like parameters. If you are trying to use the question mark in the literal sense you displayed you would need to escape it with a back slash. See below...
CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
SET NOCOUNT ON
INSERT INTO #temp
EXEC sp_msforeachtable 'sp_spaceused "\?"'
SELECT *
FROM #temp
Ben Sullins
bensullins.com
Beer is my primary key...
April 16, 2008 at 4:29 pm
hi,
thaxx to both of u really thaxx but still i got error from these queries .
There is an error in the query. Invalid object name '#temp'.
April 16, 2008 at 4:31 pm
That error is only when trying to run the query inside the data set window right? If you try running the report it shouldn't be a problem...SSRS doesn't like temp tables...
Ben Sullins
bensullins.com
Beer is my primary key...
April 16, 2008 at 5:08 pm
Ben Sullins (4/16/2008)
Tung,You cannot insert into a table variable via stored proc call...
With all due respect - it seems that you can. It certainly works for me...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 5:53 pm
Can you post an example of where that works? The example Tung provided produces the following error for me...
SET NOCOUNT ON
declare @Space table (table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
INSERT @Space
EXEC sp_msforeachtable 'sp_spaceused ''?'''
select *
from @Space
-- Results in:
Msg 197, Level 15, State 1, Line 14
EXECUTE cannot be used as a source when inserting into a table variable.
Ben Sullins
bensullins.com
Beer is my primary key...
April 16, 2008 at 6:35 pm
Ben Sullins (4/16/2008)
Can you post an example of where that works? The example Tung provided produces the following error for me...
SET NOCOUNT ON
declare @Space table (table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
INSERT @Space
EXEC sp_msforeachtable 'sp_spaceused ''?'''
select *
from @Space
-- Results in:
Msg 197, Level 15, State 1, Line 14
EXECUTE cannot be used as a source when inserting into a table variable.
And I paste the code you have here as is, and it works... What version of SQL server are you running? I'm on SQL 2005 developer SP2 CU6
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 7:29 pm
Ah yeah...that must be it, try on SQL 2000 SP4...
Ben Sullins
bensullins.com
Beer is my primary key...
April 16, 2008 at 9:05 pm
Oop.. didn't realize it doesn't work on SS2000; I tried it locally on SS2005 SP2.
I'm pretty careful about always testing code before I post to the forums.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply