March 27, 2008 at 5:53 pm
Is this possible on SSRS 2005? In the dataset command mode; would it be possible to write a Transact-SQL statements and also execute a stored procedure from the same dataset. eg.
Command Type Text in the dataset TAB:
SELECT * FROM Employees ---returns dataset to the .rdl populates the table control
EXEC dbo.insert_into_table_Sprocs --will do some other task without returing any data (parameter is passed to this SPROC)
Thanks in advance.
-Tari
March 27, 2008 at 9:07 pm
Write your own stored procedure that does both. Call that procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 5:12 am
... or try the following (don't use * though, not a good practice - use named list instead):
="SELECT * FROM Employees; EXEC dbo.insert_into_table_Sprocs @parameter=" & Parameters!XXX.Value
Give that a try. Play around with the order of the statements if it doesn't work at first (put the SELECT stmt last).
CommandType should be Text.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 28, 2008 at 5:27 am
You would need to return the data as the output of a sp , as the data set will only return the results from the first statement.
I guess this is because it displays only a single data set.
In other words write a proc that return a result set that is part of both the select and the exec statements and call that proc in the report
Jay
March 28, 2008 at 8:18 am
="SELECT * FROM Employees; EXEC dbo.insert_into_table_Sprocs @parameter=" & Parameters!XXX.Value
Marios answer is what I'm trying to do. I'll give it a try. SELECT is returning the dataset for my controls.....and the passing parameters to a proc. The proc is basically reused by all the report to insert into a log file table in the database.
Thanks!
-Tash
March 28, 2008 at 8:31 am
I should have this in a command type text or as an expression?? You have a "=". By the way the I have named list in the SELECT...I used an example.
="SELECT * FROM Employees; EXEC dbo.insert_into_table_Sprocs @parameter=" & Parameters!XXX.Value
Thanks!
-Tash
March 28, 2008 at 1:12 pm
lalonacademy (3/28/2008)
I should have this in a command type text or as an expression?? You have a "=". By the way the I have named list in the SELECT...I used an example.="SELECT * FROM Employees; EXEC dbo.insert_into_table_Sprocs @parameter=" & Parameters!XXX.Value
Thanks!
-Tash
In the Edit Selected Dataset dialog enter the above SQL in the query string box at the bottom of the dialog. Make sure Command Type right above is set to Text.
Make sense?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 28, 2008 at 1:16 pm
Also, is your parameter an int or string type. If the latter, you will have to take quotes into account, like this:
="SELECT * FROM Employees; EXEC dbo.insert_into_table_Sprocs @parameter='" & Parameters!XXX.Value & "'"
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply