network error

Hi,
Running a stored procedure in query analyser returns data but when running the asp.net page which uses that stored procedure returns an exception which is:
General network error. Check your network documentation

Do you know what the problem could be please?
ThanksNetwork errors are your SQL Server is installed with the local systems account which leaves SQL Server Agent without Network permissions. The solution you have to install SQL Server with a service account. Hope this helps.

Kind regards,
Gift Peddie|||Hi,
Are you sure this is the solution?
It's just that the query works for some and not for all of the queries that are passed through the network.
Thanks|||Installing SQL Server with a service account is good practice in all but free standing developer boxes because after SQL Server Service, SQL Server Agent is the next most important service in SQL Server. Some distributed queries will fail and you cannot run Replication because the Local Systems account leaves SQL Server Agent without Network permissions. Hope this helps.

Kind regards,
Gift Peddie

Nested Stored Procedure?

I have a stored procedure that returns a list of userIds that are available to the logged in user. I need to be able to use this list of userIds in another stored procedure whose purpose is to simply query a table for all results containing any of those userIds.

So if my first Stored Procedure returns this:
2
3
5
6

I need my select statement to do something like this:

select UserId, Column1, Column2
from Table1
where UserId = 2 or UserId = 3 or UserId = 5 or UserId = 6

I'm very new to stored procedures, can anyone help me with the syntax for doing this. I'm being pressured to get this done very quickly.

Thanks for any help!Huh? User ids like SQL Server User ids, or something application specific?

-PatP|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n
Is it possible to use Option 1 with a stored procedure instead of a select statement?|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S

Thanks! I should have thought of that. :) Its working great now.

Nested SELECT query that also returns COUNT from related table

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!Use an aggregate query:

select Category.CatID,
Category.Name,
...
count(distinct Product.ProductID) ProductCount
from Categories
left outer join Products on Categories.CategoryID = Products.CategoryID
group by Category.CatID,
Category.Name,
...
order by count(distinct Product.ProductID)|||Absolutely brilliant, it works fantastically, thank you so much!! :D

Now to try and figure out how it actually works :)|||If you use Books Online to figure out how this query works, you can consider yourself to have passed SQL 101. It incorporates the most fundamental aspects of SQL programming.

Nested Data Regions

I have two datasets. One returns say a list of employees. The second
returns sales details for each of the employees. I want to create a list
with the employee information displayed and then put a nested data region
(table) of the second data set in the list.
I can't figure for the life of me how to get this to work. When I do this,
things just don't match up right. how do I link dataset 1 to dataset 2 or
the list to the nested table?You cannot perform a join within the report itself. You need to do the join
within your query (i.e. you should only have one dataset).
If for some reason it is impossible to do the join in your query (e.g. these
are different databases and you cannot make use of SQL Servers linked table
and OpenRowset capabilities), your only remaining option is to put the
second query in a subreport and hand in a parameter from the main query to
select the corresponding rows. However, the subreport approach is not very
efficient.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Becker" <Becker@.discussions.microsoft.com> wrote in message
news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
>I have two datasets. One returns say a list of employees. The second
> returns sales details for each of the employees. I want to create a list
> with the employee information displayed and then put a nested data region
> (table) of the second data set in the list.
> I can't figure for the life of me how to get this to work. When I do
> this,
> things just don't match up right. how do I link dataset 1 to dataset 2 or
> the list to the nested table?|||What is this I read about nested data regions then? This article makes me
think I can do exactly what I'm describing:
http://www.dotnetspider.com/technology/kbpages/690.aspx
"Robert Bruckner [MSFT]" wrote:
> You cannot perform a join within the report itself. You need to do the join
> within your query (i.e. you should only have one dataset).
> If for some reason it is impossible to do the join in your query (e.g. these
> are different databases and you cannot make use of SQL Servers linked table
> and OpenRowset capabilities), your only remaining option is to put the
> second query in a subreport and hand in a parameter from the main query to
> select the corresponding rows. However, the subreport approach is not very
> efficient.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> >I have two datasets. One returns say a list of employees. The second
> > returns sales details for each of the employees. I want to create a list
> > with the employee information displayed and then put a nested data region
> > (table) of the second data set in the list.
> >
> > I can't figure for the life of me how to get this to work. When I do
> > this,
> > things just don't match up right. how do I link dataset 1 to dataset 2 or
> > the list to the nested table?
>
>|||You can use nested data regions (list, table, matrix, chart) as long as they
are based of the same dataset. You can nest subreports inside data regions -
but in general it is more efficient to perform a join in the dataset instead
of using a high number of subreport instances.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Becker" <Becker@.discussions.microsoft.com> wrote in message
news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> What is this I read about nested data regions then? This article makes me
> think I can do exactly what I'm describing:
> http://www.dotnetspider.com/technology/kbpages/690.aspx
>
> "Robert Bruckner [MSFT]" wrote:
>> You cannot perform a join within the report itself. You need to do the
>> join
>> within your query (i.e. you should only have one dataset).
>> If for some reason it is impossible to do the join in your query (e.g.
>> these
>> are different databases and you cannot make use of SQL Servers linked
>> table
>> and OpenRowset capabilities), your only remaining option is to put the
>> second query in a subreport and hand in a parameter from the main query
>> to
>> select the corresponding rows. However, the subreport approach is not
>> very
>> efficient.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Becker" <Becker@.discussions.microsoft.com> wrote in message
>> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
>> >I have two datasets. One returns say a list of employees. The second
>> > returns sales details for each of the employees. I want to create a
>> > list
>> > with the employee information displayed and then put a nested data
>> > region
>> > (table) of the second data set in the list.
>> >
>> > I can't figure for the life of me how to get this to work. When I do
>> > this,
>> > things just don't match up right. how do I link dataset 1 to dataset 2
>> > or
>> > the list to the nested table?
>>|||Bummer, I guess I can try the subreport idea, but that too me seems
confusing. I know with Business Objects, this is very easy to do, you link
multiple data providers and it magically combines the sets of data for you so
you can do some cool things with it. Oh well...maybe in reporting services
2005?
"Robert Bruckner [MSFT]" wrote:
> You can use nested data regions (list, table, matrix, chart) as long as they
> are based of the same dataset. You can nest subreports inside data regions -
> but in general it is more efficient to perform a join in the dataset instead
> of using a high number of subreport instances.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > What is this I read about nested data regions then? This article makes me
> > think I can do exactly what I'm describing:
> >
> > http://www.dotnetspider.com/technology/kbpages/690.aspx
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You cannot perform a join within the report itself. You need to do the
> >> join
> >> within your query (i.e. you should only have one dataset).
> >>
> >> If for some reason it is impossible to do the join in your query (e.g.
> >> these
> >> are different databases and you cannot make use of SQL Servers linked
> >> table
> >> and OpenRowset capabilities), your only remaining option is to put the
> >> second query in a subreport and hand in a parameter from the main query
> >> to
> >> select the corresponding rows. However, the subreport approach is not
> >> very
> >> efficient.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> >> >I have two datasets. One returns say a list of employees. The second
> >> > returns sales details for each of the employees. I want to create a
> >> > list
> >> > with the employee information displayed and then put a nested data
> >> > region
> >> > (table) of the second data set in the list.
> >> >
> >> > I can't figure for the life of me how to get this to work. When I do
> >> > this,
> >> > things just don't match up right. how do I link dataset 1 to dataset 2
> >> > or
> >> > the list to the nested table?
> >>
> >>
> >>
>
>|||I am facing a similar problem. Let me make sure I understand, again
using Becker's initial case as an example:
> I have two datasets. One returns say a list of employees. The second
> returns sales details for each of the employees.
Let's say I have ten columns in the employee table, including address,
phone number, etc. The sales details table has another 15 columns with
all of the order details.
By saying I need to join all the data in one dataset, this means that
for each row my query returns, I am going to need to return all of the
employee data for each unique sales detail row? So, if I have only 1
employee, and he has sold 1,000 items, I'm going to have to get that
employee's full data in my dataset 1,000 times, when each time it will
be exactly the same?
This may not seem that bad in this simple example, but in reality I
have about 10 tables, each with 10-15 fields, and some of these tables
will return thousands of rows. Besides using their IDs for linking
purposes, there is no logical connection between this data; each is
clearly associated with a different entity, as it is above with
Employee and Sales Detail.
Is the only way to do this with subreports or am I missing something?
Thanks,
Curt
Becker wrote:
> Bummer, I guess I can try the subreport idea, but that too me seems
> confusing. I know with Business Objects, this is very easy to do, you link
> multiple data providers and it magically combines the sets of data for you so
> you can do some cool things with it. Oh well...maybe in reporting services
> 2005?
> "Robert Bruckner [MSFT]" wrote:
> > You can use nested data regions (list, table, matrix, chart) as long as they
> > are based of the same dataset. You can nest subreports inside data regions -
> > but in general it is more efficient to perform a join in the dataset instead
> > of using a high number of subreport instances.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > > What is this I read about nested data regions then? This article makes me
> > > think I can do exactly what I'm describing:
> > >
> > > http://www.dotnetspider.com/technology/kbpages/690.aspx
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > >> You cannot perform a join within the report itself. You need to do the
> > >> join
> > >> within your query (i.e. you should only have one dataset).
> > >>
> > >> If for some reason it is impossible to do the join in your query (e.g.
> > >> these
> > >> are different databases and you cannot make use of SQL Servers linked
> > >> table
> > >> and OpenRowset capabilities), your only remaining option is to put the
> > >> second query in a subreport and hand in a parameter from the main query
> > >> to
> > >> select the corresponding rows. However, the subreport approach is not
> > >> very
> > >> efficient.
> > >>
> > >> -- Robert
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >>
> > >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> > >> >I have two datasets. One returns say a list of employees. The second
> > >> > returns sales details for each of the employees. I want to create a
> > >> > list
> > >> > with the employee information displayed and then put a nested data
> > >> > region
> > >> > (table) of the second data set in the list.
> > >> >
> > >> > I can't figure for the life of me how to get this to work. When I do
> > >> > this,
> > >> > things just don't match up right. how do I link dataset 1 to dataset 2
> > >> > or
> > >> > the list to the nested table?
> > >>
> > >>
> > >>
> >
> >
> >|||Curt, I think that is what they are saying. I think it is bunk personally,
seems to me it'd have the ability to join disperate datasets on a key(s)
value, e.g.whatever is common between tho data sets, in this case the
employee ID.
I have gotten the sub report to work, seems to be working ok. It's a little
odd, but it seems to do what I need it to do. My master report has a list
region in it and then my sub repor is a table that accepts parameters from
the calling report.
"Curt" wrote:
> I am facing a similar problem. Let me make sure I understand, again
> using Becker's initial case as an example:
> > I have two datasets. One returns say a list of employees. The second
> > returns sales details for each of the employees.
> Let's say I have ten columns in the employee table, including address,
> phone number, etc. The sales details table has another 15 columns with
> all of the order details.
> By saying I need to join all the data in one dataset, this means that
> for each row my query returns, I am going to need to return all of the
> employee data for each unique sales detail row? So, if I have only 1
> employee, and he has sold 1,000 items, I'm going to have to get that
> employee's full data in my dataset 1,000 times, when each time it will
> be exactly the same?
> This may not seem that bad in this simple example, but in reality I
> have about 10 tables, each with 10-15 fields, and some of these tables
> will return thousands of rows. Besides using their IDs for linking
> purposes, there is no logical connection between this data; each is
> clearly associated with a different entity, as it is above with
> Employee and Sales Detail.
> Is the only way to do this with subreports or am I missing something?
> Thanks,
> Curt
>
> Becker wrote:
> > Bummer, I guess I can try the subreport idea, but that too me seems
> > confusing. I know with Business Objects, this is very easy to do, you link
> > multiple data providers and it magically combines the sets of data for you so
> > you can do some cool things with it. Oh well...maybe in reporting services
> > 2005?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > You can use nested data regions (list, table, matrix, chart) as long as they
> > > are based of the same dataset. You can nest subreports inside data regions -
> > > but in general it is more efficient to perform a join in the dataset instead
> > > of using a high number of subreport instances.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > > news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > > > What is this I read about nested data regions then? This article makes me
> > > > think I can do exactly what I'm describing:
> > > >
> > > > http://www.dotnetspider.com/technology/kbpages/690.aspx
> > > >
> > > >
> > > > "Robert Bruckner [MSFT]" wrote:
> > > >
> > > >> You cannot perform a join within the report itself. You need to do the
> > > >> join
> > > >> within your query (i.e. you should only have one dataset).
> > > >>
> > > >> If for some reason it is impossible to do the join in your query (e.g.
> > > >> these
> > > >> are different databases and you cannot make use of SQL Servers linked
> > > >> table
> > > >> and OpenRowset capabilities), your only remaining option is to put the
> > > >> second query in a subreport and hand in a parameter from the main query
> > > >> to
> > > >> select the corresponding rows. However, the subreport approach is not
> > > >> very
> > > >> efficient.
> > > >>
> > > >> -- Robert
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >>
> > > >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > > >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> > > >> >I have two datasets. One returns say a list of employees. The second
> > > >> > returns sales details for each of the employees. I want to create a
> > > >> > list
> > > >> > with the employee information displayed and then put a nested data
> > > >> > region
> > > >> > (table) of the second data set in the list.
> > > >> >
> > > >> > I can't figure for the life of me how to get this to work. When I do
> > > >> > this,
> > > >> > things just don't match up right. how do I link dataset 1 to dataset 2
> > > >> > or
> > > >> > the list to the nested table?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>

need xsi:nil="true" in xml output using SQL 2000

Hi,
I have a stored Procedure that returns XML using FOR XML Explicit clause.
The xml is then validated (by appending header etc. in a c# application)
using a XSD schema.
The problem I am facing is that if I leave date and numeric fields as NULL
the schema validation fails.
How can I set the output to contain the xsi:nil="true" attribute?
Somewhere on this forum I saw that this can be achieved using FOR XML
ELEMENTS XSINIL, but on sql 2005.
How can I achieve this on sQL 2000?
Two ways:
1. add the necessary columns to your FOR XML EXPLICIT query to generate the
xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
the element.
E.g.,
select 1 as tag, 0 as parent,
CustomerID as "Customer!1!id",
'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
NULL as "Region!2!xsi:nil",
NULL as "Region!2!"
from Customers
union all
select 2 as tag, 1 as parent,
CustomerID,
NULL,
CASE WHEN Region is NULL THEN
'true'
ELSE
'false'
END,
Region
from Customers
order by "Customer!1!id"
for xml explicit
or (of course recommended ;-))
2. upgrade to SQL Server 2005.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
> Hi,
> I have a stored Procedure that returns XML using FOR XML Explicit clause.
> The xml is then validated (by appending header etc. in a c# application)
> using a XSD schema.
> The problem I am facing is that if I leave date and numeric fields as NULL
> the schema validation fails.
> How can I set the output to contain the xsi:nil="true" attribute?
> Somewhere on this forum I saw that this can be achieved using FOR XML
> ELEMENTS XSINIL, but on sql 2005.
> How can I achieve this on sQL 2000?
>
|||Is there an easier way to do this in the C# code once i have the XML result
from the stored procedure?
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate the
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>
|||Depends on what you consider easy. You would have to scan through the XML
and identify missing elements or elements with an empty or specially marked
content and change them... you could do it using an XSLT transform or some
C# code. I would consider that more complex, but then I am used to a
declarative way of generating data.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:26970D6F-D704-4DD9-A43F-39A8CCE77FBA@.microsoft.com...[vbcol=seagreen]
> Is there an easier way to do this in the C# code once i have the XML
> result
> from the stored procedure?
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have been at this thing for hours but can't figure out how to Query the
following table, [Customers] to result in the required XML (below):
[Customers]
ID RegionName
CustomerName
__________________________________________________ ____________
1 North
Nabeel
1 NULL
Nabeel
2 North
NULL
3 NULL
NULL
3 South
Nabeel
[XML OUTPUT]
<root>
<Customer>
<Id>1</Id>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>North</RegionName>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName xsi:nil="true"/>
</Region>
</Customer>
<Customer>
<Id>2</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName>North</RegionName>
</Region>
</Customer>
<Customer>
<Id>3</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName xsi:nil="true"/>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>South</RegionName>
</Region>
</Customer>
</root>
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate the
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>
|||Hi Nabeel, sorry for the late reply... I hope this is still useful.
Here is the explicit mode solution. Note that you need a uniquefier for the
Region ID so you get different region elements.
select 1 as tag, NULL as parent,
1 as "root!1!id!hide",
'http://www.w3.org/2001/XMLSchema-instance' as
"root!1!xmlns:xsi",
NULL as "Customer!2!Id!element",
NULL as "Region!3!dummy!hide",
NULL as "CustomerName!4!",
NULL as "CustomerName!4!xsi:nil",
NULL as "RegionName!5!",
NULL as "RegionName!5!xsi:nil"
union all
select 2 as tag, 1 as parent,
1, NULL, /*root*/
c1.ID, /*Customer*/
NULL, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from (select distinct ID from Customers) c1
union all
select 3 as tag, 2 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 4 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
CustomerName,
CASE WHEN CustomerName is NULL
THEN 'true'
ELSE NULL END, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 5 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
RegionName,
CASE WHEN RegionName is NULL
THEN 'true'
ELSE NULL END /*RegionName*/
from Customers
order by "root!1!id!hide", "Customer!2!Id!element", "Region!3!dummy!hide",
tag
for xml explicit
and here for people using SQL Server 2005, the much simpler FOR XML PATH.
select c1.ID as "Id",
(select CustomerName, RegionName
from Customers c2
where c2.ID=c1.ID
for xml path('Region'), type, elements xsinil)
from (select distinct ID from Customers) c1
for xml path('Customer'), root
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:A189B17C-814E-4BD1-8A73-B5BA304E2985@.microsoft.com...[vbcol=seagreen]
> Michael,
> I have been at this thing for hours but can't figure out how to Query the
> following table, [Customers] to result in the required XML (below):
> [Customers]
> ID RegionName
> CustomerName
> __________________________________________________ ____________
> 1 North
> Nabeel
> 1 NULL
> Nabeel
> 2 North
> NULL
> 3 NULL
> NULL
> 3 South
> Nabeel
>
> [XML OUTPUT]
>
> <root>
> <Customer>
> <Id>1</Id>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>North</RegionName>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName xsi:nil="true"/>
> </Region>
> </Customer>
> <Customer>
> <Id>2</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName>North</RegionName>
> </Region>
> </Customer>
> <Customer>
> <Id>3</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName xsi:nil="true"/>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>South</RegionName>
> </Region>
> </Customer>
> </root>
> "Michael Rys [MSFT]" wrote:

need xsi:nil="true" in xml output using SQL 2000

Hi,
I have a stored Procedure that returns XML using FOR XML Explicit clause.
The xml is then validated (by appending header etc. in a c# application)
using a XSD schema.
The problem I am facing is that if I leave date and numeric fields as NULL
the schema validation fails.
How can I set the output to contain the xsi:nil="true" attribute?
Somewhere on this forum I saw that this can be achieved using FOR XML
ELEMENTS XSINIL, but on sql 2005.
How can I achieve this on sQL 2000?Two ways:
1. add the necessary columns to your FOR XML EXPLICIT query to generate the
xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
the element.
E.g.,
select 1 as tag, 0 as parent,
CustomerID as "Customer!1!id",
'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
NULL as "Region!2!xsi:nil",
NULL as "Region!2!"
from Customers
union all
select 2 as tag, 1 as parent,
CustomerID,
NULL,
CASE WHEN Region is NULL THEN
'true'
ELSE
'false'
END,
Region
from Customers
order by "Customer!1!id"
for xml explicit
or (of course recommended ;-))
2. upgrade to SQL Server 2005.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
> Hi,
> I have a stored Procedure that returns XML using FOR XML Explicit clause.
> The xml is then validated (by appending header etc. in a c# application)
> using a XSD schema.
> The problem I am facing is that if I leave date and numeric fields as NULL
> the schema validation fails.
> How can I set the output to contain the xsi:nil="true" attribute?
> Somewhere on this forum I saw that this can be achieved using FOR XML
> ELEMENTS XSINIL, but on sql 2005.
> How can I achieve this on sQL 2000?
>|||Is there an easier way to do this in the C# code once i have the XML result
from the stored procedure?
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate th
e
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute
on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>|||Depends on what you consider easy. You would have to scan through the XML
and identify missing elements or elements with an empty or specially marked
content and change them... you could do it using an XSLT transform or some
C# code. I would consider that more complex, but then I am used to a
declarative way of generating data.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:26970D6F-D704-4DD9-A43F-39A8CCE77FBA@.microsoft.com...
> Is there an easier way to do this in the C# code once i have the XML
> result
> from the stored procedure?
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I have been at this thing for hours but can't figure out how to Query the
following table, [Customers] to result in the required XML (below):
[Customers]
ID RegionName
CustomerName
________________________________________
______________________
1 North
Nabeel
1 NULL
Nabeel
2 North
NULL
3 NULL
NULL
3 South
Nabeel
[XML OUTPUT]
<root>
<Customer>
<Id>1</Id>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>North</RegionName>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName xsi:nil="true"/>
</Region>
</Customer>
<Customer>
<Id>2</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName>North</RegionName>
</Region>
</Customer>
<Customer>
<Id>3</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName xsi:nil="true"/>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>South</RegionName>
</Region>
</Customer>
</root>
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate th
e
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute
on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>|||Hi Nabeel, sorry for the late reply... I hope this is still useful.
Here is the explicit mode solution. Note that you need a uniquefier for the
Region ID so you get different region elements.
select 1 as tag, NULL as parent,
1 as "root!1!id!hide",
'http://www.w3.org/2001/XMLSchema-instance' as
"root!1!xmlns:xsi",
NULL as "Customer!2!Id!element",
NULL as "Region!3!dummy!hide",
NULL as "CustomerName!4!",
NULL as "CustomerName!4!xsi:nil",
NULL as "RegionName!5!",
NULL as "RegionName!5!xsi:nil"
union all
select 2 as tag, 1 as parent,
1, NULL, /*root*/
c1.ID, /*Customer*/
NULL, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from (select distinct ID from Customers) c1
union all
select 3 as tag, 2 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 4 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
CustomerName,
CASE WHEN CustomerName is NULL
THEN 'true'
ELSE NULL END, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 5 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
RegionName,
CASE WHEN RegionName is NULL
THEN 'true'
ELSE NULL END /*RegionName*/
from Customers
order by "root!1!id!hide", "Customer!2!Id!element", "Region!3!dummy!hide",
tag
for xml explicit
and here for people using SQL Server 2005, the much simpler FOR XML PATH.
select c1.ID as "Id",
(select CustomerName, RegionName
from Customers c2
where c2.ID=c1.ID
for xml path('Region'), type, elements xsinil)
from (select distinct ID from Customers) c1
for xml path('Customer'), root
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:A189B17C-814E-4BD1-8A73-B5BA304E2985@.microsoft.com...
> Michael,
> I have been at this thing for hours but can't figure out how to Query the
> following table, [Customers] to result in the required XML (below):
> [Customers]
> ID RegionName
> CustomerName
> ________________________________________
______________________
> 1 North
> Nabeel
> 1 NULL
> Nabeel
> 2 North
> NULL
> 3 NULL
> NULL
> 3 South
> Nabeel
>
> [XML OUTPUT]
>
> <root>
> <Customer>
> <Id>1</Id>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>North</RegionName>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName xsi:nil="true"/>
> </Region>
> </Customer>
> <Customer>
> <Id>2</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName>North</RegionName>
> </Region>
> </Customer>
> <Customer>
> <Id>3</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName xsi:nil="true"/>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>South</RegionName>
> </Region>
> </Customer>
> </root>
> "Michael Rys [MSFT]" wrote:
>

Need View to return "NULL" values

I have a view that is joining multiple tables. How do I modify this view so
that it also returns when the value is "NULL" for some of the join table
fields?
Any help would be greatly appreciated!!!
Thank you,
-Valerie
SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
dbo.DOSAGEUNIT.DosageUnitDS,
dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
dbo.DURATIONUNIT.DurationUnitDS,
dbo.PATIENTMEDICATION.DurationUnitID,
dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
dbo.PATIENTMEDICATION.DiscontinuedDT,
dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
dbo.PATIENTMEDICATION.PatientID
FROM dbo.PATIENTMEDICATION INNER JOIN
dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID =
dbo.MEDICATION.MedicationID INNER JOIN
dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID =
dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
dbo.DURATIONUNIT ON
dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN DESC,
dbo.PATIENTMEDICATION.PatientMedicationIDTake a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
instructions. This is probably what you want.
The use of an UNION query can also be usefull for this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>I have a view that is joining multiple tables. How do I modify this view
>so
> that it also returns when the value is "NULL" for some of the join table
> fields?
> Any help would be greatly appreciated!!!
> Thank you,
> -Valerie
> SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
> dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
> dbo.DOSAGEUNIT.DosageUnitDS,
> dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
> dbo.DURATIONUNIT.DurationUnitDS,
> dbo.PATIENTMEDICATION.DurationUnitID,
> dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
> dbo.PATIENTMEDICATION.DiscontinuedDT,
> dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
> dbo.PATIENTMEDICATION.PatientID
> FROM dbo.PATIENTMEDICATION INNER JOIN
> dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID
> =
> dbo.MEDICATION.MedicationID INNER JOIN
> dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID
> =
> dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
> dbo.DURATIONUNIT ON
> dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
> ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN
> DESC,
> dbo.PATIENTMEDICATION.PatientMedicationID|||>> I have a view that is joining multiple tables. How do I modify this vie=
w so that it also returns when the value is "NULL" for some of the join tab=
le fields [sic]? <<
Fileds and columns are different; one of the MANY differences is that a
column can have a NULL. You probably want an OUTER JOIN, but what you
posted looks wrong from a design viewpoint. If we had DDL we could
more.
1) What is a "medication_id" -- don't you have an industry standard
drug code? I seem to remember that such a thing exists.
2) What makes "medication_id" in medication a totally different thing
from a "patient_m=ADedication_id"? You never, never give the same
data elements different names in the same schema. And doing by
physical storage locations is really bad.
3) Why are units of measuresment modeled as entities and not
attributes? Do you see a gram walking around, independent of an
entity? Unless units of time and medication change independently and
frequently, they ought to be part of the dosage, not entities. You
might want to a add a CHECK() constrint to the DDL to assure this
attribute is correct.
4) Did you actually use an 'y/n' flag in SQL like we did with punch
cards? Remember the rule about storing computed data values? Do not
do it.
5) Can you explain the logical differences between a
"duration=AD_unit_id" and mere "duration=AD_unit" (ditto dosage)? If
you read any book on data modeling or ISO-11179 that first name is
absurd. An identifier gives you unique entity and unit is a scale for
an attribute; entities are not attributes.
6) Why do you have duration, start and discontinue times in the table?
You can compute duration, can't you?|||Thank you! The LEFT OUTER JOIN was exactly what I needed. You have made my
day. :-)
Thanks again,
Valerie
"Sylvain Lafontaine" wrote:

> Take a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
> instructions. This is probably what you want.
> The use of an UNION query can also be usefull for this kind of problem.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
> news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>
>|||Thank you for your input - you mentioned many good points/topics; most of
which are already taken into consideration with our current data model.
"--CELKO--" wrote:

> Fileds and columns are different; one of the MANY differences is that a
> column can have a NULL. You probably want an OUTER JOIN, but what you
> posted looks wrong from a design viewpoint. If we had DDL we could
> more.
> 1) What is a "medication_id" -- don't you have an industry standard
> drug code? I seem to remember that such a thing exists.
> 2) What makes "medication_id" in medication a totally different thing
> from a "patient_m_edication_id"? You never, never give the same
> data elements different names in the same schema. And doing by
> physical storage locations is really bad.
> 3) Why are units of measuresment modeled as entities and not
> attributes? Do you see a gram walking around, independent of an
> entity? Unless units of time and medication change independently and
> frequently, they ought to be part of the dosage, not entities. You
> might want to a add a CHECK() constrint to the DDL to assure this
> attribute is correct.
> 4) Did you actually use an 'y/n' flag in SQL like we did with punch
> cards? Remember the rule about storing computed data values? Do not
> do it.
> 5) Can you explain the logical differences between a
> "duration__unit_id" and mere "duration__unit" (ditto dosage)? If
> you read any book on data modeling or ISO-11179 that first name is
> absurd. An identifier gives you unique entity and unit is a scale for
> an attribute; entities are not attributes.
> 6) Why do you have duration, start and discontinue times in the table?
> You can compute duration, can't you?
>

Need to UPDATE with PARAMETER / PARTITION / RANK. Please help.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Assuming ID is the primary key, you can just do something like this:

update dbo.acmdtn
set Active = 1
where id in (
SELECT id
FROM (SELECT dbo.acmdtn.*,
row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1) )
and active <> 1