Sql Ignoring spaces: regarding

Showing posts with label regarding. Show all posts

Friday, March 23, 2012

Image Server Database

I am a semi-experienced DBA, but have some questions regarding the
storage of images inside of SQL Server (which I have never done before).
I am researching to pros and cons of storing images inside of SQL
Server verses a file server and DB combination (storing the file paths
in SQL). There is a push from my management to store all the images
within a very large database, but something is telling me that it's not
the best way to do it. Unfortunately, I don't have any concrete
experience/evidence to push in one direction verses another.
We are talking about the storage/cataloging of around 4 million images.
Our current image server is file-based and is around 1 tera in size.
The idea of moving all this into a large SQL Server database raises
allot of concern for me.
I guess I would just like some feed-back from others that may have
experience using SQL Server to house a large amount of images. I would
also like to hear if anyone may have pros/cons to one concept verses
another. Any help would be greatly appreciated.
http://www.aspfaq.com/show.asp?id=2149
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I am a semi-experienced DBA, but have some questions regarding the
> storage of images inside of SQL Server (which I have never done before).
> I am researching to pros and cons of storing images inside of SQL
> Server verses a file server and DB combination (storing the file paths
> in SQL). There is a push from my management to store all the images
> within a very large database, but something is telling me that it's not
> the best way to do it. Unfortunately, I don't have any concrete
> experience/evidence to push in one direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size.
> The idea of moving all this into a large SQL Server database raises
> allot of concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.
|||Hello Justin,
We used to have lot of Contracts that needed to be scanned in the insurance
application
and we used FileNET imaging server.
Gopi
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
>I am a semi-experienced DBA, but have some questions regarding the storage
>of images inside of SQL Server (which I have never done before). I am
>researching to pros and cons of storing images inside of SQL Server verses
>a file server and DB combination (storing the file paths in SQL). There is
>a push from my management to store all the images within a very large
>database, but something is telling me that it's not the best way to do it.
>Unfortunately, I don't have any concrete experience/evidence to push in one
>direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size. The
> idea of moving all this into a large SQL Server database raises allot of
> concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.

Image Server Database

I am a semi-experienced DBA, but have some questions regarding the
storage of images inside of SQL Server (which I have never done before).
I am researching to pros and cons of storing images inside of SQL
Server verses a file server and DB combination (storing the file paths
in SQL). There is a push from my management to store all the images
within a very large database, but something is telling me that it's not
the best way to do it. Unfortunately, I don't have any concrete
experience/evidence to push in one direction verses another.
We are talking about the storage/cataloging of around 4 million images.
Our current image server is file-based and is around 1 tera in size.
The idea of moving all this into a large SQL Server database raises
allot of concern for me.
I guess I would just like some feed-back from others that may have
experience using SQL Server to house a large amount of images. I would
also like to hear if anyone may have pros/cons to one concept verses
another. Any help would be greatly appreciated.http://www.aspfaq.com/show.asp?id=2149
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I am a semi-experienced DBA, but have some questions regarding the
> storage of images inside of SQL Server (which I have never done before).
> I am researching to pros and cons of storing images inside of SQL
> Server verses a file server and DB combination (storing the file paths
> in SQL). There is a push from my management to store all the images
> within a very large database, but something is telling me that it's not
> the best way to do it. Unfortunately, I don't have any concrete
> experience/evidence to push in one direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size.
> The idea of moving all this into a large SQL Server database raises
> allot of concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.|||Hello Justin,
We used to have lot of Contracts that needed to be scanned in the insurance
application
and we used FileNET imaging server.
Gopi
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
>I am a semi-experienced DBA, but have some questions regarding the storage
>of images inside of SQL Server (which I have never done before). I am
>researching to pros and cons of storing images inside of SQL Server verses
>a file server and DB combination (storing the file paths in SQL). There is
>a push from my management to store all the images within a very large
>database, but something is telling me that it's not the best way to do it.
>Unfortunately, I don't have any concrete experience/evidence to push in one
>direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size. The
> idea of moving all this into a large SQL Server database raises allot of
> concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.

Image Server Database

I am a semi-experienced DBA, but have some questions regarding the
storage of images inside of SQL Server (which I have never done before).
I am researching to pros and cons of storing images inside of SQL
Server verses a file server and DB combination (storing the file paths
in SQL). There is a push from my management to store all the images
within a very large database, but something is telling me that it's not
the best way to do it. Unfortunately, I don't have any concrete
experience/evidence to push in one direction verses another.
We are talking about the storage/cataloging of around 4 million images.
Our current image server is file-based and is around 1 tera in size.
The idea of moving all this into a large SQL Server database raises
allot of concern for me.
I guess I would just like some feed-back from others that may have
experience using SQL Server to house a large amount of images. I would
also like to hear if anyone may have pros/cons to one concept verses
another. Any help would be greatly appreciated.http://www.aspfaq.com/show.asp?id=2149
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I am a semi-experienced DBA, but have some questions regarding the
> storage of images inside of SQL Server (which I have never done before).
> I am researching to pros and cons of storing images inside of SQL
> Server verses a file server and DB combination (storing the file paths
> in SQL). There is a push from my management to store all the images
> within a very large database, but something is telling me that it's not
> the best way to do it. Unfortunately, I don't have any concrete
> experience/evidence to push in one direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size.
> The idea of moving all this into a large SQL Server database raises
> allot of concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.|||Hello Justin,
We used to have lot of Contracts that needed to be scanned in the insurance
application
and we used FileNET imaging server.
Gopi
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
>I am a semi-experienced DBA, but have some questions regarding the storage
>of images inside of SQL Server (which I have never done before). I am
>researching to pros and cons of storing images inside of SQL Server verses
>a file server and DB combination (storing the file paths in SQL). There is
>a push from my management to store all the images within a very large
>database, but something is telling me that it's not the best way to do it.
>Unfortunately, I don't have any concrete experience/evidence to push in one
>direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size. The
> idea of moving all this into a large SQL Server database raises allot of
> concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.

Image Render Problem

Hi,
I still have problem in rendering the Images.
I have found some suggestion regarding the Rendering the Images from the earlier posts http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp?frame=true
But Still my image comes with Red Cross Symbol. I can see the image created and persisted on the directory I have mentioned with StreamRoot Tag but the problem still persists. The Img Src also shows the right path but the Image does n't come up. Is due to the absense of extension name in the image file is the cause of the problem.
I appreciate your assistance,
Environment : Windows2003 Server/Reporting Services(no sp1)/IE 6
Thanks in Advance,
HolyHi
I also tried the other suggestion with Rendering Reports with External Images.
I have set the HTMLFragment Property to True and with no renderstream call.
There it seems to be partially working because of the name of Image Path is referred always of localhost. So I can see the image on the development machine but not from the other machines since the image src is referred in localhost path.
And this is the image path it is rendered.
http://localhost/ReportServer?%2fBuyBackReports%2fEmissionSummary&rs%3aSessionID=ztzoiz55mcsvgy55ha12tsya&rs%3aFormat=HTML4.0&rs%3aImageID=a6b7d061-27a8-48a6-9a31-fd1b2ce04e93
Now how can I change the localhost path?
Thanks Again,
Holysmoke
"Holysmoke" wrote:
> Hi,
> I still have problem in rendering the Images.
> I have found some suggestion regarding the Rendering the Images from the earlier posts http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp?frame=true
> But Still my image comes with Red Cross Symbol. I can see the image created and persisted on the directory I have mentioned with StreamRoot Tag but the problem still persists. The Img Src also shows the right path but the Image does n't come up. Is due to the absense of extension name in the image file is the cause of the problem.
> I appreciate your assistance,
> Environment : Windows2003 Server/Reporting Services(no sp1)/IE 6
> Thanks in Advance,
> Holy
>|||Hi,
You've probably checked this but your server name doesn't contain an underscore does it?
ken
"Holysmoke" wrote:
> Hi,
> I still have problem in rendering the Images.
> I have found some suggestion regarding the Rendering the Images from the earlier posts http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp?frame=true
> But Still my image comes with Red Cross Symbol. I can see the image created and persisted on the directory I have mentioned with StreamRoot Tag but the problem still persists. The Img Src also shows the right path but the Image does n't come up. Is due to the absense of extension name in the image file is the cause of the problem.
> I appreciate your assistance,
> Environment : Windows2003 Server/Reporting Services(no sp1)/IE 6
> Thanks in Advance,
> Holy
>|||Do your charts not show up either? We had an underscore in our server
name (sql_server) and graphics and charts would not show on the
server. We removed the underscore, and all is well.
Holysmoke <Holysmoke@.discussions.microsoft.com> wrote in message news:<41C4557D-7872-45F7-8628-944F08EA28B6@.microsoft.com>...
> Hi,
> I still have problem in rendering the Images.
> I have found some suggestion regarding the Rendering the Images from the earlier posts http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp?frame=true
> But Still my image comes with Red Cross Symbol. I can see the image created and persisted on the directory I have mentioned with StreamRoot Tag but the problem still persists. The Img Src also shows the right path but the Image does n't come up. Is due to the absense of extension name in the image file is the cause of the problem.
> I appreciate your assistance,
> Environment : Windows2003 Server/Reporting Services(no sp1)/IE 6
> Thanks in Advance,
> Holy|||Hi,
I am using the renderStream method, and charts does not appear
because IMG tag in the rendered report link to an image file name without
extension.
Have you found a solution for this ?
Tanks a lot
"Holysmoke" wrote:
> Hi,
> I still have problem in rendering the Images.
> I have found some suggestion regarding the Rendering the Images from the earlier posts http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp?frame=true
> But Still my image comes with Red Cross Symbol. I can see the image created and persisted on the directory I have mentioned with StreamRoot Tag but the problem still persists. The Img Src also shows the right path but the Image does n't come up. Is due to the absense of extension name in the image file is the cause of the problem.
> I appreciate your assistance,
> Environment : Windows2003 Server/Reporting Services(no sp1)/IE 6
> Thanks in Advance,
> Holy
>sql

Monday, March 12, 2012

Im having a bit of problem regarding XML output

Im having a bit of problem regarding XML output.
Im trying to make the info nested, like so:
- < product >
<soap>
<toothpaste>
and so on..
but instead Im getting it like this:
- < product >
<soap>
- < product >
<toothpaste>
I know i am problably forgetting some simple and stupid thing, but u know
how it is..

the code itself:
SELECT Rei.Hotel,
Rei.Payment,
Rei.InvoiceNr,
Rei.Total245,
Rei.Total14,
Rei.base245,
Rei.Base14,
Rei.Total_inc_tax,
prod.PrID,
prod.Price
FROM IceToStrengView AS Rei
LEFT OUTER JOIN
(
SELECT products.product AS PrID,
products.RNR,
products.customer,
products.Item AS Price
FROM Customer_brought AS products
) AS prod ON Rei.customerID = prod.customer AND Rei.RNR = prod.RNR
WHERE Rei.date BETWEEN (GETDATE()-1)
AND GETDATE()
|||Hi gang, thanks for the help, but now I have another problem, as you can
see all the "products ReiknID" are displayed together, they
do not come nested under the correct record... any help here :/ ?
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="Streaming not supported over multiple column result"?>
<Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324" />
<Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325" />
- <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
Total_inc_tax="0" Date="2004-06-09T13:39:31.967" bokunarNr="R991250\011"
ReiknNr="994199" Payment="1" InnriRID="7323">
<products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
<products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
<products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
<products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
<products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
<products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
<products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
<products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
<products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
<products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
<products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
<products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
<products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
</Rei>
</ROOT>
it should be like this
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="Streaming not supported over multiple column result"?>
<Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324" />
<products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
<products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
<products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
<products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
<products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
<products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
<Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325" />
<products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
<products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
<products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
<products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
<products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
<products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
<products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
- <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
Total_inc_tax="0" Date="2004-06-09T13:39:31.967" bokunarNr="R991250\011"
ReiknNr="994199" Payment="1" InnriRID="7323">
</Rei>
</ROOT>
this is the code
SELECT
1 as tag,
null as parent,
IceToStrengView.Hotel as [Rei!1!Hotel],
IceToStrengView.Total245 AS [Rei!1!Total245],
IceToStrengView.Total14 AS [Rei!1!Total14],
IceToStrengView.base245 AS [Rei!1!base245],
IceToStrengView.Base14 AS [Rei!1!Base14],
IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
IceToStrengView.Date AS [Rei!1!Date],
IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
IceToStrengView.Payment AS [Rei!1!Payment],
IceToStrengView.InnriRID AS [Rei!1!InnriRID],
null as [products!2!ReiknID],
null as [products!2!PrID],
null as [products!2!Price],
null as [products!2!Quantity]
FROM IceToStrengView
WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
Union All
SELECT
2,
1,
Hotel,
Total245,
Total14,
base245,
Base14,
Total_inc_tax,
Date,
bokunarNr,
ReiknNr,
Payment,
InnriRID,
tmpIceToStreng.ReiknID,
tmpIceToStreng.PrID,
tmpIceToStreng.Price,
tmpIceToStreng.Quantity
FROM IceToStrengView
INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
tmpIceToStreng.ReiknID
ORDER BY [Rei!1!Hotel],
[products!2!ReiknID]
FOR XML EXPLICIT
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:CAC21219-D90D-452D-AE0B-EAF4E84417E0@.microsoft.com...
> Hlynur,
> Have you considered using FOR XML EXPLICIT? Explicit mode gives you
greater control over the XML that SQL is generating. In Explicit mode, you
can use the UNION clause to create a "universal table". You should also try
adding a root (parent) element to your XML.
> Here is a Northwind example using FOR XML EXPLICIT with a root element.
Also, a great reference for learning more about EXPLICIT mode can be found
at http://www.topxml.com/sql/for_xml_explicit.asp.
> SELECT
> 1 AS Tag,
> NULL AS Parent,
> Customers.CustomerID AS [Customer!1!CustomerID!hide],
> Customers.CompanyName AS [Customer!1!CompanyName!element],
> Customers.Address AS [Customer!1!Address!element],
> Customers.City AS [Customer!1!City!element],
> Customers.Region AS [Customer!1!Region!element],
> Customers.PostalCode AS [Customer!1!PostalCode!element],
> Customers.Country AS [Customer!1!Country!element],
> Customers.ContactName AS [Customer!1!ContactName!element],
> NULL AS [Order!2!OrderID],
> NULL AS [Order!2!ShipVia],
> NULL AS [OrderDetail!3!ProductID],
> NULL AS [OrderDetail!3!ProductName],
> NULL AS [OrderDetail!3!Quantity]
> FROM
> Customers
> UNION ALL
> -- 2. Second level of the hierarchy.
> SELECT
> 2,
> 1,
> Customers.CustomerID,
> Customers.CompanyName,
> Customers.Address,
> Customers.City,
> Customers.Region,
> Customers.PostalCode,
> Customers.Country,
> Customers.ContactName,
> Orders.OrderID,
> Shippers.CompanyName,
> NULL,
> NULL,
> NULL
> FROM
> Customers
> JOIN
> Orders
> ON
> Customers.CustomerID = Orders.CustomerID
> JOIN
> Shippers
> ON
> Orders.ShipVia = Shippers.ShipperID
> UNION ALL
> -- 3. Third level of the hierarchy.
> SELECT
> 3,
> 2,
> Orders.CustomerID,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Orders.OrderID,
> NULL,
> [Order Details].ProductID,
> Products.ProductName,
> [Order Details].Quantity
> FROM
> Orders
> JOIN
> [Order Details]
> ON
> Orders.OrderID = [Order Details].OrderID
> JOIN
> Products
> ON
> [Order Details].ProductID = Products.ProductID
> ORDER BY
> [Customer!1!CustomerID!hide],
> [Order!2!OrderID],
> [OrderDetail!3!ProductID]
> FOR XML EXPLICIT
>
>
> -- Hlynur r Jnasson wrote: --
> Im having a bit of problem regarding XML output.
> Im trying to make the info nested, like so:
> - < product ><soap><toothpaste>
> and so on..
> but instead Im getting it like this:
> - < product ><soap>
> - < product ><toothpaste>
> I know i am problably forgetting some simple and stupid thing, but u
know
> how it is..

> the code itself:
> SELECT Rei.Hotel,
> Rei.Payment,
> Rei.InvoiceNr,
> Rei.Total245,
> Rei.Total14,
> Rei.base245,
> Rei.Base14,
> Rei.Total_inc_tax,
> prod.PrID,
> prod.Price
> FROM IceToStrengView AS Rei
> LEFT OUTER JOIN
> (
> SELECT products.product AS PrID,
> products.RNR,
> products.customer,
> products.Item AS Price
> FROM Customer_brought AS products
> ) AS prod ON Rei.customerID = prod.customer AND Rei.RNR =
prod.RNR
> WHERE Rei.date BETWEEN (GETDATE()-1)
> AND GETDATE()
>
>
|||This means that you have the wrong order by or forgot to add/repeat the
parent's identifying value that you order by and/or you order on the wrong
value.
In your case, you order on the wrong value, the [Rei!1!Hotel] value is not
unique, thus all the ones with value 1 is added before you get to the
products.
Try:
SELECT 1 as tag, null as parent,
IceToStrengView.Hotel as [Rei!1!Hotel],
IceToStrengView.Total245 AS [Rei!1!Total245],
IceToStrengView.Total14 AS [Rei!1!Total14],
IceToStrengView.base245 AS [Rei!1!base245],
IceToStrengView.Base14 AS [Rei!1!Base14],
IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
IceToStrengView.Date AS [Rei!1!Date],
IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
IceToStrengView.Payment AS [Rei!1!Payment],
IceToStrengView.InnriRID AS [Rei!1!InnriRID],
null as [products!2!ReiknID],
null as [products!2!PrID],
null as [products!2!Price],
null as [products!2!Quantity]
FROM IceToStrengView
WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
Union All
SELECT 2, 1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
InnriRID,
tmpIceToStreng.ReiknID,
tmpIceToStreng.PrID,
tmpIceToStreng.Price,
tmpIceToStreng.Quantity
FROM IceToStrengView
INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
tmpIceToStreng.ReiknID
ORDER BY [Rei!1!InnriRID], [products!2!ReiknID]
FOR XML EXPLICIT
HTH
Michael
"Hlynur r Jnasson" <hlybbi@.xodus.net> wrote in message
news:O8moDouTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi gang, thanks for the help, but now I have another problem, as you can
> see all the "products ReiknID" are displayed together, they
> do not come nested under the correct record... any help here :/ ?
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
> Server" Description="Streaming not supported over multiple column
> result"?>
> <Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
> Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
> bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324"
> />
> <Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
> Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
> bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325"
> />
> - <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
> Total_inc_tax="0" Date="2004-06-09T13:39:31.967"
> bokunarNr="R991250\011"
> ReiknNr="994199" Payment="1" InnriRID="7323">
> <products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
> <products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
> <products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
> <products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
> <products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
> <products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
> <products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
> <products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
> <products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
> <products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
> <products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
> <products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
> <products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
> </Rei>
> </ROOT>
> it should be like this
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
> Server" Description="Streaming not supported over multiple column
> result"?>
> <Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
> Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
> bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324"
> />
> <products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
> <products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
> <products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
> <products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
> <products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
> <products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
> <Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
> Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
> bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325"
> />
> <products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
> <products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
> <products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
> <products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
> <products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
> <products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
> <products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
> - <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
> Total_inc_tax="0" Date="2004-06-09T13:39:31.967"
> bokunarNr="R991250\011"
> ReiknNr="994199" Payment="1" InnriRID="7323">
> </Rei>
> </ROOT>
>
> this is the code
> SELECT
> 1 as tag,
> null as parent,
> IceToStrengView.Hotel as [Rei!1!Hotel],
> IceToStrengView.Total245 AS [Rei!1!Total245],
> IceToStrengView.Total14 AS [Rei!1!Total14],
> IceToStrengView.base245 AS [Rei!1!base245],
> IceToStrengView.Base14 AS [Rei!1!Base14],
> IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
> IceToStrengView.Date AS [Rei!1!Date],
> IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
> IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
> IceToStrengView.Payment AS [Rei!1!Payment],
> IceToStrengView.InnriRID AS [Rei!1!InnriRID],
> null as [products!2!ReiknID],
> null as [products!2!PrID],
> null as [products!2!Price],
> null as [products!2!Quantity]
> FROM IceToStrengView
> WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
> Union All
> SELECT
> 2,
> 1,
> Hotel,
> Total245,
> Total14,
> base245,
> Base14,
> Total_inc_tax,
> Date,
> bokunarNr,
> ReiknNr,
> Payment,
> InnriRID,
> tmpIceToStreng.ReiknID,
> tmpIceToStreng.PrID,
> tmpIceToStreng.Price,
> tmpIceToStreng.Quantity
> FROM IceToStrengView
> INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
> tmpIceToStreng.ReiknID
>
> ORDER BY [Rei!1!Hotel],
> [products!2!ReiknID]
>
> FOR XML EXPLICIT
>
>
> "mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
> news:CAC21219-D90D-452D-AE0B-EAF4E84417E0@.microsoft.com...
> greater control over the XML that SQL is generating. In Explicit mode, you
> can use the UNION clause to create a "universal table". You should also
> try
> adding a root (parent) element to your XML.
> Also, a great reference for learning more about EXPLICIT mode can be found
> at http://www.topxml.com/sql/for_xml_explicit.asp.
> know
> prod.RNR
>

Sunday, February 19, 2012

IID_IDBDataSourceAdmin Error Trying to Create a Database using Query Analyzer on a Mobile Device

Hi,

Please provide some help regarding the "Interface Defining Error: IID_IDBDataSourceAdmin" error while trying to create a SDF database using Query Analyzer on a Windows CE 5.0 mobile device (Symbol MC3000).

Error: 0x80004005 E_FAIL

Native Error: 28558

Description: SQL Mobile encountered problems when creating database [,,,,]

Param. 0: 0

Param. 1: 0

Param. 2: 0

Param. 3:

Param. 4:

Param. 5:

A list of (related) installed packages:

NETCFv2.wce5.armv4i.cab

sqlce30.dev.ENU.wce5.armv4i.CAB

sqlce30.repl.wce5.armv4i.CAB

sqlce30.wce5.armv4i.CAB

PS.

Basically I have developed a mobile application that programmatically creates the database, the code worked on a similar device (Win CE 50), trying to run the application on a new device resulted in database creation errors. I tried creating a test database manually .. and this is what I got.

Browsing MSDN or searching on the Forum did not help.

~Zarko Gajic

AH!

Problem "solved". The device was in its cradle BUT no battery was installed!

When the battery was inserted I was able to create the database using Query Analyzer.

However, I now have additional problems:

Trying to create the database programmatically (using SQlCeEngine.CreateDatabase) results in:

ErrorCode: 8007000E

Minor Code 28558.

Again I can not find any meaningful info on this.

~Zarko Gajic

Friday, March 23, 2012

Image Server Database

Image Server Database

Image Server Database

Image Server Database

Image Render Problem

Monday, March 12, 2012

Im having a bit of problem regarding XML output

Sunday, February 19, 2012

IID_IDBDataSourceAdmin Error Trying to Create a Database using Query Analyzer on a Mobile Device

Sql Ignoring spaces

Blog Archive

About Me