Dynamic field question

Can someone point me in the right direction to solve the following
(basic) SQL problem below using SQL Server:

Let's say I have a table like this that lists people's likes:

CREATE TABLE likes (
myname VARCHAR (60),
travel BIT,
eatingout BIT,
disco BIT,
swimming BIT);

Let's say I put the following data inside this table:

INSERT INTO likes VALUES ('Darren', 1,0,0,1)
INSERT INTO likes VALUES ('John',1,1,0,1)
INSERT INTO likes VALUES ('Peter',0,0,0,0)
INSERT INTO likes VALUES ('Jill',0,0,0,1)

Then what I want is to create a (view? Or function? I am not sure),
called likes_details' that when I send this query:

SELECT myname, likes FROM likes_details

Returns the following:

Myname likes
DarrenTraveling and swimming
JohnTraveling, eating out and swimming
PeterDone not like anything
JillSwimming only

Please! Can anyone help!!

Thank you in advance.Darren,

The mess-ed up short term workaround to your problem with the existing
schema is:

SELECT myName,
COALESCE( NULLIF (
CASE travel WHEN 1 THEN 'Travelling, '
ELSE SPACE(0) END +
CASE eatingout WHEN 1 THEN 'eating out, '
ELSE SPACE(0) END +
CASE disco WHEN 1 THEN 'disco, '
ELSE SPACE(0) END +
CASE swimming WHEN 1 THEN 'swimming, '
ELSE SPACE(0) END, SPACE(0)),
'Do not like anything')
FROM likes ;

Now, the real solution to your problem is that you need to overhaul your
schema, it has values as column names, under-normalized and thus unusable. A
good way of representing this information would be like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
PersonName VARCHAR(10) NOT NULL,
...);
CREATE TABLE Hobbies (
Hobby_id INT NOT NULL PRIMARY KEY,
HobbyDesc VARCHAR(20) NOT NULL,
...);
CREATE TABLE PersonHobbies(
Person_id INT NOT NULL
REFERENCES Persons(Person_id),
Hobby_id INT NOT NULL
REFERENCES Hobbies(Hobby_id)
PRIMARY KEY (Person_id, Hobby_id)) ;

The primary keys in the Persons table & Hobbies tables are assigned with the
assumption that there could be other relevant attributes associated with
these entities, otherwise using Name & Desc as keys are just fine. The data
for these tables, based on the information you provided could be like:

INSERT Persons SELECT 1, 'Darren' ;
INSERT Persons SELECT 2, 'John' ;
INSERT Persons SELECT 3, 'Peter' ;
INSERT Persons SELECT 4, 'Jill' ;
GO
INSERT Hobbies SELECT 1, 'travel' ;
INSERT Hobbies SELECT 2, 'eatingout' ;
INSERT Hobbies SELECT 3, 'disco' ;
INSERT Hobbies SELECT 4, 'swimming' ;
GO
INSERT PersonHobbies SELECT 1, 1 ;
INSERT PersonHobbies SELECT 1, 4 ;
INSERT PersonHobbies SELECT 2, 1 ;
INSERT PersonHobbies SELECT 2, 2 ;
INSERT PersonHobbies SELECT 2, 4 ;
INSERT PersonHobbies SELECT 4, 4 ;
GO

The above schema represents a m-to-m relationship between Persons and
Hobbies. It allows you to add persons and hobbies to the system without
having to alter the tables and facilitates efficient querying. Now, you can
have a SQL statement like:

SELECT p1.PersonName, h1.HobbyDesc, ...
FROM Persons p1
LEFT OUTER JOIN PersonHobbies ph1
ON p1.Person_id = ph1.Person_id
LEFT OUTER JOIN Hobbies h1
ON h1.Hobby_id = ph1.Hobby_id

Get the resultset to your client application & cross tab the data to the
format with comma, add words like "and" etc for the requirements for
display.

--
Anith

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec' Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC' THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount offIf you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec'
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC'
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get th
e
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü?meyer" wrote:

> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
>
>|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:

> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec' Meaning
,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, tha
t
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC' THank
s,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =
coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.err
or
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
>

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec? Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC? THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount off
If you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec?
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC?
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>
|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get the
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü?meyer" wrote:

> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
>
>
|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:

> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec? Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC? THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>
|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =
coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec' Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC' THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount offIf you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec'
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC'
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get the
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü�meyer" wrote:
> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> > When doing a basic EXEC statement, is there any straight-forward way of
> > dealing with error handling about the statement inside the exec'
> > Meaning,
> > if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> > not that the code WITHIN the EXEC ran ok. I threw this example below,
> > that
> > does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> > a
> > #table works, but this seems a bit much to code for. Any other ideas to
> > shorten the code but be able to do error handling within the EXEC'
> > THanks,
> > Bruce
> >
> > set nocount on
> > drop table #err
> > create table #err (error_no int)
> > declare @.cmd varchar(255), @.errno int
> > select @.errno = 0
> > select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> > char(39) + 'mroXXXir' + char(39)
> > + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> > (error_no) select @.errno_2'
> > select @.cmd
> > exec (@.cmd)
> > select @.errno = @.@.error
> > select @.errno as 'Error Code from the EXEC'
> > select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> > set nocount off
> >
> >
>
>|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec' Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC' THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
> > When doing a basic EXEC statement, is there any straight-forward way of
> > dealing with error handling about the statement inside the exec' Meaning,
> > if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> > not that the code WITHIN the EXEC ran ok. I threw this example below, that
> > does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> > #table works, but this seems a bit much to code for. Any other ideas to
> > shorten the code but be able to do error handling within the EXEC' THanks,
> > Bruce
> >
> > set nocount on
> > drop table #err
> > create table #err (error_no int)
> > declare @.cmd varchar(255), @.errno int
> > select @.errno = 0
> > select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> > char(39) + 'mroXXXir' + char(39)
> > + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> > (error_no) select @.errno_2'
> > select @.cmd
> > exec (@.cmd)
> > select @.errno = @.@.error
> > select @.errno as 'Error Code from the EXEC'
> > select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> > set nocount off
> >
> >

Dynamic default value for parameter in OLAP report

I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.

Thanks for any ideas.

-Josh R.

At parameter properties page in section "Default values" check radio button "Non-queried" and then at Expression box write:

=Today

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

|||Hi,

I would suggest you to go ahead create a datasource through ReportManager.
By this way you can make the Reports more flexible and scalable.
I did it in the same way and it did work out well as it was more flexible.
Do not forget to make the datasource shared one.

Regards,
Vamsi Krishna Korasiga.

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

dynamic creation of select statement

hello,

My basic requirement is i have five drop down list where data is selected and based on the selection the no. of colums to be selected have to be designed. ex : say i have following drop down lists:
1. community
2. gender
3. level
4. term
5. year

at runtime is it possible to design the crystal reports columns.

Pl. help out in directing to url or given sample source to dynamic creation of columns in the crystal reports.

thanks in advance
karunau need to add the formulas and the field object formatting.
u can do this by declaring an array -->
IFormulaFieldDefinitionPtr formulas[10];

then when adding formulas-->
ISectionPtr pSection = GetReportSection(sectionNum);
IFormulaFieldDefinitionsPtr pFormulaFields = 0;
m_Report->get_FormulaFields((IFormulaFieldDefinitions**) &pFormulaFields);

CString Recur = CString(L"WhileReadingRecords;" +
(CString)__toascii(13) +
(CString)__toascii(10));

// Add the formulas to the formula fields collection
CString CStrText = Recur + CString(L"Space(10)");
BSTR strText = CStrText.AllocSysString();
BSTR objName;
int i=0;
////assume u have stored the field names somewhere like a list or an array.

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
if(i==0)
{
objName = str.AllocSysString();
formulas[i]=pFormulaFields->Add(objName, strText);


}
else
{
SysReAllocString(&objName, str.AllocSysString());
formulas[i]=pFormulaFields->Add(objName, strText);

}

i++;
}

////////////////////////////////////////////////////////////////////////////////
then add the field objects-->

ISectionPtr pSection = GetReportSection(3);
IFieldObjectPtr pFieldObj = 0;

LocX = 10; // Horizontal offset
VARIANT var;
VariantInit (&var);
var.vt = VT_DISPATCH;
BSTR objName = NULL;

int i=0;

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
var.pdispVal = formulas[i];
pFieldObj = pSection->AddFieldObject(var, LocX, 0);
SysReAllocString(&objName,str.AllocSysString());
pFieldObj->put_Name(objName);
pFieldObj->put_Width(1600);
LocX += 1500;
}

////////////////////////////////////////////////////////////////////////////////////////////
thats it! hope this helps...