Showing posts with label server2005. Show all posts
Showing posts with label server2005. Show all posts

Monday, March 12, 2012

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources(), the row
returned has the server name in column [0] and nothing in the other 3 columns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Hi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSource s();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSource s();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical should
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code into
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||

Quote:

Originally Posted by Charles Wang[MSFT]View Post

Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.

I am experiencing exactly the same behavior.
This code returns the Server and Instance names correctly on my local box (I have three instances installed locally, all with instance names).
However, it returns blank fields for every server on the network (there are a great many servers on the network). I know for a fact that many of these have instance names, but the instance name comes back as null in this code.
So this is definitely something that is not isolated to just the first poster who reported this behavior.
Are there ANY other ideas here? Any suggestions of what might be going on? It sure looks like a bug from my end, but could it be anything in the internal firewalls or any settings in SQL Server? Or the fact that I'm going over VPN? Could there be any VPN or network issues?
Our environment is a mix of SQL Server 2000 and SQL Server 2005. Again, everything is fine on my local box, but the remote servers report no instance names, or versions, or anything else, ever. Just the server name comes back, exactly as the grandparent indicated.|||

Quote:

Originally Posted by Charles Wang[MSFT]View Post

Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

Is there any more information on this?
I've verified that it's definitely related to networking or VPN.
When I run the code from my VPN'd machine, I don't get back
anything but the server name (no instance name, no version,
just as originally reported by David). But if I take the same
EXE and copy it to a machine inside the actual network and
run it, it sees everything fine.
Can you verify if there are some additional ports that need
opening, or some additional settings that need to be made?
Surely someone ELSE can reproduce this problem, since two
of us experience it so reproducably...
Anyone? Bueller?

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources(), the row
returned has the server name in column [0] and nothing in the other 3 co
lumns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmHi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSources();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSources();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n
";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical shoul
d
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code int
o
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>|||Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

Friday, March 9, 2012

I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.
The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn/...l/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegr oups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>
|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1
]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[
1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact
/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNu
mber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)
[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contac
t/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)
[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/To
talAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn...ml/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
--
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegroups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)
[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2
)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Conta
ct/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]'
,
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrder
Number)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name
2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Cont
act/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name
1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/
TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

Wednesday, March 7, 2012

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL Server2005 Standard Edition with my SBS2003R2

How can I get the SQL Server2005 Standard Edition when I upgrade my SBS2003 to SBS2003R2?

I'm no expert on SBS but it looks like upgrading to R2 gets you SQL Server 2005 Workgroup if you're on SBS Premium. Upgrading from Workgroup to Standard would probably entail purchasing SQL STD and then doing the install. But I have no idea if this would be a good idea or if it would even be supported by SBS. You should ask your question on their forum.

Paul

|||

Yup you need to buy SQL 2005 standard separately along with the cals

Come over to the newsgroup at microsoft.public.windows.server.sbs

I need SQL server2005 materials

hi every body ;

Ineed sql server 2005 material

Can you please be more specific?|||

mindshunter:

hi every body ;

Ineed sql server 2005 material

Try the link below to download the latest BOL(books online) run a search for all you need to know. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

|||

I need sql2005 DB implemnting ,DB maintenance &DB security

|||

mindshunter:

I need sql2005 DB implemnting ,DB maintenance &DB security

Run a search for all of the above in the BOL (books online) after you install it.

|||

Thanks for reply

mindshunter