Parent Child Tables

In our database we have a list of devices in a "Device" Table, each
having one or more IP's located in the "IP" Table linked through a
forein key on the DeviceID Column.

I would like to retrieve this information as Such

DeviceID IpAddress
1 10.0.0.1, 10.0.0.2, 10.0.0.3
2 ...
3
4
5
etc.

Is it possible to do that without using cursors? Through a query?"Mark" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0402231034.7facd986@.posting.google.c om...
> In our database we have a list of devices in a "Device" Table, each
> having one or more IP's located in the "IP" Table linked through a
> forein key on the DeviceID Column.
> I would like to retrieve this information as Such
> DeviceID IpAddress
> 1 10.0.0.1, 10.0.0.2, 10.0.0.3
> 2 ...
> 3
> 4
> 5
> etc.
> Is it possible to do that without using cursors? Through a query?

There are several ways to do this (see the link below for one solution) but
in general it's easier to do this in a client application - formatting data
for presentation shouldn't usually be done in SQL code.

http://www.aspfaq.com/show.asp?id=2279

Note that this solution cannot guarantee to sort the data in any particular
order (see the KB article referenced from the link), so if that is a
requirement then you will need to use a cursor or client code.

Simon

parametrised servername and catalog name

Hi,

I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.

While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.

The question is: can I use variables for the first two parts of a four-part table name. Something like:

declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table

Would the above work ?

I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.

Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,

eg.

set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''

insert into #master_id (user_id)
exec sp_executesql @.statement

though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params

Parameters don't appear in drilledthrough report

Hi,
we created a "Start"-Report with a drillthrough via "Jump to Report" to an
"Linked"-Report
and both have parameters.
If we drillthrough from the Start-Report to the Linked-Report on the
SQL Server September CTP Reporting Services the parameters are not visibile
and we are
not able to make them visible. If we call the Linked-Report directly,
the parameters appear.
In the June CTP and in the preview of the September CTP everything works
fine and the parameters of both reports appear every time.
Any help will be appreciated.
RalphI'm running SQL2000 SP4 and Reporting Services SP2 but I'm having the same
problem on some reports. Others work fine. If you get an answer to this
problem please let me know. jlowther@.sta-home.com
--
John W. Lowther, Jr.
DBA / Senior Developer
Sta-home Health Agency
"bjh" wrote:
> Hi,
> we created a "Start"-Report with a drillthrough via "Jump to Report" to an
> "Linked"-Report
> and both have parameters.
> If we drillthrough from the Start-Report to the Linked-Report on the
> SQL Server September CTP Reporting Services the parameters are not visibile
> and we are
> not able to make them visible. If we call the Linked-Report directly,
> the parameters appear.
> In the June CTP and in the preview of the September CTP everything works
> fine and the parameters of both reports appear every time.
> Any help will be appreciated.
> Ralph
>
>|||John,
our problem was solved by the final release of SQL Server 2005.
It looks like is has been a bug in the September CTP.
The workaround used was to utilize "Jump to URL" instead of "Jump to Report"
Björn
"John Lowther" <johnlowther@.online.nospam> schrieb im Newsbeitrag
news:185FCD01-E94C-42DE-AD32-1335ECF7C158@.microsoft.com...
> I'm running SQL2000 SP4 and Reporting Services SP2 but I'm having the same
> problem on some reports. Others work fine. If you get an answer to this
> problem please let me know. jlowther@.sta-home.com
> --
> John W. Lowther, Jr.
> DBA / Senior Developer
> Sta-home Health Agency
>
> "bjh" wrote:
>> Hi,
>> we created a "Start"-Report with a drillthrough via "Jump to Report" to
>> an
>> "Linked"-Report
>> and both have parameters.
>> If we drillthrough from the Start-Report to the Linked-Report on the
>> SQL Server September CTP Reporting Services the parameters are not
>> visibile
>> and we are
>> not able to make them visible. If we call the Linked-Report directly,
>> the parameters appear.
>> In the June CTP and in the preview of the September CTP everything works
>> fine and the parameters of both reports appear every time.
>> Any help will be appreciated.
>> Ralph
>>