Extracting Sql: bit

Showing posts with label bit. Show all posts

Monday, March 19, 2012

Failed in installation, ERROR:29503

Hi, Folks

My story is a bit of similar to some of the fellows. During the default installation, it failed at the last section which is "SQL server database service" because it cannot start the SQL service.

The difference of my story is this is not the first time I install it on my computer or any upgrade from whatever other version. I'd installed it for a couple of weeks working perfectly before my computer seemingly infected by an adware. Since then SQL Server stopped working, then I decided to re-install it after I had the adware removed.

So it shouldn't be the limitation of either CPU or memory, I think. I hope I could get an answer to it before I have to re-install the whole OS, PAINFULLY.

CONTENT OF SUMMARY.LOG

Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Mon Jul 10 22:53:32 2006
Machine : YANG
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQLSupport_1.log
--
Machine : YANG
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQLNCLI_1.log
--
Machine : YANG
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SqlWriter_1.log
--
Machine : YANG
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_MSXML6_1.log
--
Machine : YANG
Product : SQL Server Database Services
Error : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How

to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
--
Machine : YANG
Product : SQL Server Database Services
Error : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How

to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
--
Machine : YANG
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQL.log
Last Action : InstallFinalize
Error String : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How

to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is (1067) The process terminated

unexpectedly.
Error Number : 29503
--

SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%\Microsoft SQL Server\90\Setup

Bootstrap\LOG\Summary.txt.

Time : Mon Jul 10 22:57:30 2006

List of log files:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_Core(Local).log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQLSupport_1.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQLNCLI_1.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SqlWriter_1.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_MSXML6_1.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SQL.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_Datastore.xml
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_.NET Framework 2.0.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SNAC.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_Core.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_Support.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0022_YANG_SCC.log
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

|||

CONTENT OF ERROR.LOG

2006-07-10 22:56:35.05 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-07-10 22:56:35.05 Server (c) 2005 Microsoft Corporation.
2006-07-10 22:56:35.05 Server All rights reserved.
2006-07-10 22:56:35.05 Server Server process ID is 504.
2006-07-10 22:56:35.05 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-07-10 22:56:35.05 Server Registry startup parameters:
2006-07-10 22:56:35.05 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-07-10 22:56:35.05 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-07-10 22:56:35.05 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-07-10 22:56:35.05 Server Command Line Startup Parameters:
2006-07-10 22:56:35.05 Server -m SqlSetup
2006-07-10 22:56:35.05 Server SqlSetup
2006-07-10 22:56:35.05 Server -Q
2006-07-10 22:56:35.05 Server -q Chinese_PRC_CI_AS
2006-07-10 22:56:35.05 Server -T 4022
2006-07-10 22:56:35.05 Server -T 3659
2006-07-10 22:56:35.05 Server -T 3610
2006-07-10 22:56:35.05 Server -T 4010
2006-07-10 22:56:35.44 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-07-10 22:56:35.44 Server Detected 1 CPUs. This is an informational message; no user action is required.
2006-07-10 22:56:35.47 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-07-10 22:56:35.55 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-07-10 22:56:35.56 spid5s Warning ******************
2006-07-10 22:56:35.56 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2006-07-10 22:56:35.56 spid5s Starting up database 'master'.
2006-07-10 22:56:35.79 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-07-10 22:56:36.44 spid5s SQL Trace ID 1 was started by login "sa".
2006-07-10 22:56:36.50 spid5s Starting up database 'mssqlsystemresource'.
2006-07-10 22:56:37.25 Server Error: 26011, Severity: 16, State: 1.
2006-07-10 22:56:37.25 Server The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system.
2006-07-10 22:56:37.25 Server Error: 17182, Severity: 16, State: 1.
2006-07-10 22:56:37.25 Server TDSSNIClient initialization failed with error 0x139f, status code 0x80.
2006-07-10 22:56:37.25 Server Error: 17182, Severity: 16, State: 1.
2006-07-10 22:56:37.25 Server TDSSNIClient initialization failed with error 0x139f, status code 0x1.
2006-07-10 22:56:37.25 Server Error: 17826, Severity: 18, State: 3.
2006-07-10 22:56:37.25 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2006-07-10 22:56:37.25 Server Error: 17120, Severity: 16, State: 1.
2006-07-10 22:56:37.25 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

|||OMG, guess what... It's installed SUCCESSFULLY again just now while I tried to figure out in which part it had failed. I sware I haven't done anything specially tonight. Well, difference, tonight I'm thinking seriously of re-install my Windows XP rather than looking for solutions as what I was doing in last few nights. Oh...don't juggle with me. I don't know who, Windows XP or SQL server 2005 express edition.|||

There certainly seemed to be some problems with the installation based on your log, but if it's installed and working that's probably well enough. Tought to say if your adware incident was the cause of this, or if the problems you're seeing are related to it.

I'd say leave it be for a week to see what happens before you consider re-install of Windows, but that's your call.

Mike

|||

Hi Mike

Thanks for your eye on my topic.

The service stopped again and refused being stared manually. I checked Configuration Manager, and both SQL Server and SQL Server Browser stopped.

The ERRORLOG has same content as above.

Then I changed login account in both services from Network Service to Local System, now they are running.

Monday, March 12, 2012

Failed in installatio, ERROR:29503

Hi, Folks

My story is a bit of similar to some of the fellows. During the default installation, it failed at the last section which is "SQL server database service" because it cannot start the SQL service.

So it shouldn't be the limitation of either CPU or memory, I think. I hope I could get an answer to it before I have to re-install the whole OS, PAINFULLY.

CONTENT OF SUMMARY.LOG

to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is (1067) The process terminated

unexpectedly.
Error Number : 29503
--

SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%\Microsoft SQL Server\90\Setup

Bootstrap\LOG\Summary.txt.

Time : Mon Jul 10 22:57:30 2006

|||

CONTENT OF ERROR.LOG

I'd say leave it be for a week to see what happens before you consider re-install of Windows, but that's your call.

Mike

|||

Hi Mike

Thanks for your eye on my topic.

The service stopped again and refused being stared manually. I checked Configuration Manager, and both SQL Server and SQL Server Browser stopped.

The ERRORLOG has same content as above.

Then I changed login account in both services from Network Service to Local System, now they are running.

Wednesday, March 7, 2012

Fact and Dimension linking

Hi,

Am a bit of a beginner in this area, so this may sound a little dumb.

I am trying to develop my first cube based on consolidated sales data from 10 different companies, and have a problem wrt linking a dimension table to this fact table.

I have a dimension table which contains the products which can be manufactured by these 10 companies. There are scenarios where one product can be made in several of these companies.

I therefore have a table with all the sales line items (my fact table) which are uniquely identified by invoice no, invoice line num, and originating site.

The product table (dimension table) is uniquely identified by item code and site.

When I create my data source view I create a join between the two tables with sales.site = products.site and sales.itemcode = products.itemcode

If I then create a dimension from my products table and browse them in the cube, the sales data just gets repeated for each product and is not correctly split.

I think that the link I have between the dimension and the fact table is wrong, as when I set the relationship I can only choose one column to link on.

I'm kinda getting lost on this one, and could really do with some pointers. Restructuring the tables are not too much of a problem (if they are wrong) as the project has not been officially released yet.

Any help would be greatly appreciated.

Hello! In the cube editor you have a dimension usage tab where you configure the relation between each dimension and measure group(fact table). If you have created som dimension after the you have created the cube this relation will not be configured automatically.

It seems to me as you relations are correct in the data source view. You can have a problem with that the product key is defined without the site key in the dimension editor. You will have to make a collection of product key and site key. Check the dimension key for this property.

HTH

Thomas Ivarsson

|||

You might check the KeyColumns property for the key attribute of your Product dimension - a composite key {item code, site} should be defined. Then you should be able to join the fact table using this composite key.

http://msdn2.microsoft.com/en-us/library/ms175461.aspx

SQL Server 2005 Books Online

How to: Modify the KeyColumn Property of an Attribute

This procedure describes how to modify the KeyColumns property of an attribute. For example, you may want to specify a composite key rather than a single key as the key for the attribute.

...

|||The item code and site also have to be present in the fact table for this to work. Your message says that the invoice line num is present in the fact table - you have to be able to derive the product item code from this.|||

Thx everyone... after posting I sat down and read the book again to make sure I hadn't missed anything out. And you are right, the tables are OK in my DSV, but the dimension had not been configured correctly w.r.t. its attributes.

As soon as I had that done correctly, of course it all fell into place..

Thx again.

Sunday, February 19, 2012

extracting results of XML stored proc to file

Hi all, I need a bit of guidance!
I have created a few queries using FOR XML AUTO, but am unsure how to extract them from the results into an XML file
out side of the sql server. is there a way I can make the stored procedure push the result to an xml file on my server? Or do
I have to use ado to extract the data from the server. I would rather push than pull!
thanks in advance!
Mike
There's no easy way to do it using T-SQL (I guess you could write an
extended Stored Proc or something, but it'd be a lot of effort.)
Your best bet is to install SQLXML 3.0
(http://www.microsoft.com/downloads/d...33a9-cf10-4e22
-8004-477098a407ac&displaylang=en) and use a script like the following
example to generate the file. You could then wrap this script up in a SQL
Agent job and execute it from within SQL Server.
Example script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=localhost;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike UK" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA91318-4FF8-4DF7-B27B-0CDA3A876389@.microsoft.com...
> Hi all, I need a bit of guidance!
> I have created a few queries using FOR XML AUTO, but am unsure how to
extract them from the results into an XML file
> out side of the sql server. is there a way I can make the stored procedure
push the result to an xml file on my server? Or do
> I have to use ado to extract the data from the server. I would rather push
than pull!
> thanks in advance!
> Mike
|||Thanks Graeme, I will certainly give it a go! I must admit I m a little suprised we cant export from a stored proc,
especiall as we can get the results of a FOR XML query into the results pane.
Thanks again for the script!
Mike.
|||Hi Mike,
You can export from an SP! - We're doing it via BCP (bulk copy) and XML templates. It is a bit tedious to write but it has been working very well in our production environment for the past 3 months.
We have one stored proc that contains our FOR XML statement. For the purposes of discussion, I'll call it 'record_return' (in our case, we're using FOR XML EXPLICIT but it would not make a difference using AUTO) and another stored procedure 'launch_record
_return' that calls 'record_return'.
Within launch_record_return we build a dynamic SQL statement and execute the call to BCP. In our case, we are dynamically building the full path and file name for the exported XML.
Example (from within launch_record_return):
-- create full file path for our output XML document
SET @.xmlname = '\'+@.ord+'.xml' (in this case, @.ord is the name of the file which we're generating dynamically)
SET @.finalname = '\xx'+@.ord+'.xml'
SET @.fname=@.fullDirPath+@.xmlname
SET @.fullPathAndFinalName = @.fullDirPath+@.finalname
SET @.fname=@.fullDirPath+@.xmlname
SET @.bcpCommand = 'bcp "EXEC DB_NAME..record_return ' + "'" + @.ord + "'" + '"' +" queryout "
SET @.bcpCommand = @.bcpCommand + @.fname + ' -c -t -r'
SET @.appendCommand = 'copy/b c:\yourdirectory\header.txt+'+@.fname+'+c:\yourdire ctory\footer.txt '+ @.fullPathAndFinalName
-- execute commands
EXEC master..xp_cmdshell @.bcpCommand
EXEC master..xp_cmdshell @.appendCommand
-- delete temp case file
SET @.appendCommand = ' del ' + @.fname
EXEC master..xp_cmdshell @.appendCommand
The header.txt and footer.txt files contain the head of the document because you'll find that BCP does not wrap the XML in a root element.
This code would of course need to be revised to meet your specific requirements but should provide you with some insight on how to go about generating an actual XML file from an SP. We have also setup a DTS job that runs the 'launch' procedure because the
re is a bug (http://support.microsoft.com/default...;en-us;Q275583) that occurs when executing FOR XML statements via ODBC. We found that it was easy to get around this by creating a DTS job and connecting via OLE.
Hope this helps.
Have a great weekend.
Any questions, feel free to email me.
Dee
Denise E. White
IT Developer
Legal Marketing Services
Cheshire, England
|||Hi Denise,
I thank you for this great post. One thing I wanted to note - a way to get your XML Explicit sproc to have a root element without appending text files: start your sproc with a record that from your highest level table that you know occurs only once, per
haps a transaction identifier value, or perhaps a string value like your company name and wrap it as below
SELECT
1 AS Tag,
NULL AS Parent,
TR.TransSetCntrlNmbr AS [ROOT!1!Filler!Hide],
....
|||Thanks.This is a great post.can any one post the complete
code of the procedure?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks JT...I have been using this technique however, I still include a header and footer file beacause I cannot find a way using this method to have the root element contain a reference to my XSD schema.
Any thoughts?
Denise

Extracting from linked tables into pivot

Hi there,
I've managed to come this far and I need a bit of help to finalise. I'm
extracting records correctly using the following:
select substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Jan,
sum(case month(D.MLineShipDate)
when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Feb,
sum(case month(D.MLineShipDate)
when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Mar,
sum(case month(D.MLineShipDate)
when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Apr,
sum(case month(D.MLineShipDate)
when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as May,
sum(case month(D.MLineShipDate)
when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Jun
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
What I need to add is the quantity on hand from the InvWarehouse table. My
stock codes look something like this: 002-0300-10-WW-01 where the first 3
digits indicate the style (as in my code). In the InvWarehouse table there
are the same codes with a quantity on hand, and what I need is to sum the
total quantity on hand per style and add this field to the pivot (per style)
.
Thanking you in advance.
Kind regards,Hi
You are alredy joining to the InvWarehouse table therefore (if I understand
your problem) your summation of the quantity should be an extension of what
you have!
select substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
end) as Jan,
sum(case month(D.MLineShipDate)
when 1 then w.quantity else 0
end) as JanQuantity,
...
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
If this is not the case, Posting DDL and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
the sample data would be beneficial.
John
"CyberFox" wrote:

> Hi there,
> I've managed to come this far and I need a bit of help to finalise. I'm
> extracting records correctly using the following:
> select substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Jan,
> sum(case month(D.MLineShipDate)
> when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Feb,
> sum(case month(D.MLineShipDate)
> when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Mar,
> sum(case month(D.MLineShipDate)
> when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Apr,
> sum(case month(D.MLineShipDate)
> when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as May,
> sum(case month(D.MLineShipDate)
> when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Jun
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> What I need to add is the quantity on hand from the InvWarehouse table. My
> stock codes look something like this: 002-0300-10-WW-01 where the first 3
> digits indicate the style (as in my code). In the InvWarehouse table there
> are the same codes with a quantity on hand, and what I need is to sum the
> total quantity on hand per style and add this field to the pivot (per styl
e).
> Thanking you in advance.
> Kind regards,|||Hi John,
The summation of the quantities is not date-dependent. Let me explain
exactly what I want:
The InvWarehouse table has a quantity on hand per stock item (this is the
quantity in stock at the current time, and is not date-dependent at all).
What I want to show is the stock item (actually the style number, which is
the first 3 digits of the stock item), it's quantity on hand (the stock
item's), and the outstanding sales orders per style (date-dependent).
Hope this clarifies.
Rgds,
"John Bell" wrote:
> Hi
> You are alredy joining to the InvWarehouse table therefore (if I understan
d
> your problem) your summation of the quantity should be an extension of wha
t
> you have!
> select substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) el
se 0
> end) as Jan,
> sum(case month(D.MLineShipDate)
> when 1 then w.quantity else 0
> end) as JanQuantity,
> ...
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> If this is not the case, Posting DDL and example data would help see
> http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> the sample data would be beneficial.
> John
>
> "CyberFox" wrote:
>|||Hi
DDL, Example data and expected output would have eliminated any ambiguity
when you post. These are untested:
If you just want to sum the QuantityInHand values using the same where
clause as your main query then you can do that with
SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
SUM(D.QuantityInHand) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
If you don't want that restriction then a subquery may be needed:
SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
or possibly using derived tables and joining them
SELECT A.Style, A.Jan, A.Feb,... B.Total
FROM
( SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) A
JOIN
( SELECT substring(D.MStockCode,1,3) as Style,
SUM ( D.QuantityInHand ) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style
John
"CyberFox" wrote:
> Hi John,
> The summation of the quantities is not date-dependent. Let me explain
> exactly what I want:
> The InvWarehouse table has a quantity on hand per stock item (this is the
> quantity in stock at the current time, and is not date-dependent at all).
> What I want to show is the stock item (actually the style number, which is
> the first 3 digits of the stock item), it's quantity on hand (the stock
> item's), and the outstanding sales orders per style (date-dependent).
> Hope this clarifies.
> Rgds,
> "John Bell" wrote:
>|||John,
I've tried the sub-query option, but it didn't do what I was hoping for. Let
me simplify and give you some examples of my data, if you don't mind:
SorDetail table:
Itemcode BackOrderQty OrderDate
002-0200-10-WW-02 100 01/01/06
002-0200-11-WW-02 150 02/01/06
002-0200-12-WW-02 150 01/02/06
010-0300-16-ED-03 100 01/01/06
010-0300-16-MK-01 200 01/01/06
010-0300-16-TR-02 100 01/03/06
InvWarehouse table
ItemCode QtyOnHand
002-0200-10-WW-02 2000
002-0200-11-WW-02 1400
002-0200-12-WW-02 1500
010-0300-16-ED-03 1000
010-0300-16-MK-01 1000
010-0300-16-TR-02 1000
I need the following (considering that the style = first 3 digits of the
item codes)
Style QtyOnHand JanOrders FebOrders Mar
002 4900 250 150
0
010 3000 300 0
100
Thank you very much for your help so far...
Rgds,
"John Bell" wrote:
> Hi
> DDL, Example data and expected output would have eliminated any ambiguity
> when you post. These are untested:
> If you just want to sum the QuantityInHand values using the same where
> clause as your main query then you can do that with
> SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> SUM(D.QuantityInHand) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> If you don't want that restriction then a subquery may be needed:
> SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> ( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
> substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> or possibly using derived tables and joining them
> SELECT A.Style, A.Jan, A.Feb,... B.Total
> FROM
> ( SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) A
> JOIN
> ( SELECT substring(D.MStockCode,1,3) as Style,
> SUM ( D.QuantityInHand ) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
> ORDER BY A.Style
> John
> "CyberFox" wrote:
>|||Hi
This post is inconsistent with the tables/columns that you have posted
previously so it is even more confusing, make sure that you read
http://www.aspfaq.com/etiquette.asp?id=5006 and post something usable.
With:
CREATE TABLE SorDetail ( Itemcode char(17), BackOrderQty
int, OrderDate datetime)
CREATE TABLE InvWarehouse ( Itemcode char(17), QtyOnHand
int )
INSERT INTO SorDetail ( Itemcode, BackOrderQty, OrderDate)
SELECT '002-0200-10-WW-02', 100, '20060101'
UNION ALL SELECT '002-0200-11-WW-02', 150, '20060102'
UNION ALL SELECT '002-0200-12-WW-02', 150, '20060201'
UNION ALL SELECT '010-0300-16-ED-03', 100, '20060101'
UNION ALL SELECT '010-0300-16-MK-01', 200, '20060101'
UNION ALL SELECT '010-0300-16-TR-02', 100, '20060103
'
INSERT INTO InvWarehouse ( Itemcode, QtyOnHand )
SELECT '002-0200-10-WW-02', 2000
UNION ALL SELECT '002-0200-11-WW-02', 1400
UNION ALL SELECT '002-0200-12-WW-02', 1500
UNION ALL SELECT '010-0300-16-ED-03', 1000
UNION ALL SELECT '010-0300-16-MK-01', 1000
UNION ALL SELECT '010-0300-16-TR-02', 1000
My query:
SELECT substring(D.Itemcode,1,3) as Style,
sum(case month(D.OrderDate)
when 1 then D.BackOrderQty
else 0
end) as Jan,
sum(case month(D.OrderDate)
when 2 then D.BackOrderQty
else 0
end) as Feb,
sum(case month(D.OrderDate)
when 3 then D.BackOrderQty
else 0
end) as Mar,
( SELECT SUM(I.QtyOnHand) FROM InvWarehouse I WHERE
substring(D.Itemcode,1,3) = substring(I.Itemcode,1,3) ) AS InHand
from SorDetail D
LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
WHERE D.BackOrderQty > 0
group by substring(D.Itemcode,1,3)
order by substring(D.Itemcode,1,3)
seems to give exaclty what you required, although it gives me an error if I
change the column order, so using:
SELECT A.Style, B.QtyOnHand, A.Jan, A.Feb, A.Mar
FROM
( SELECT SUBSTRING(D.Itemcode,1,3) as Style,
SUM(CASE MONTH(D.OrderDate)
WHEN 1 THEN D.BackOrderQty
ELSE 0
END) AS Jan,
SUM(CASE MONTH(D.OrderDate)
WHEN 2 THEN D.BackOrderQty
ELSE 0
END) AS Feb,
SUM(CASE MONTH(D.OrderDate)
WHEN 3 THEN D.BackOrderQty
ELSE 0
END) AS Mar
FROM SorDetail D
LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
WHERE D.BackOrderQty > 0
GROUP BY SUBSTRING(D.Itemcode,1,3) ) A
LEFT JOIN ( SELECT SUBSTRING(i.Itemcode,1,3) as Style,
SUM(I.QtyOnHand) AS QtyOnHand
FROM InvWarehouse I
GROUP BY SUBSTRING(I.Itemcode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style
may be a better option.
John
"CyberFox" wrote:
> John,
> I've tried the sub-query option, but it didn't do what I was hoping for. L
et
> me simplify and give you some examples of my data, if you don't mind:
> SorDetail table:
> Itemcode BackOrderQty OrderDate
> 002-0200-10-WW-02 100 01/01/06
> 002-0200-11-WW-02 150 02/01/06
> 002-0200-12-WW-02 150 01/02/06
> 010-0300-16-ED-03 100 01/01/06
> 010-0300-16-MK-01 200 01/01/06
> 010-0300-16-TR-02 100 01/03/06
> InvWarehouse table
> ItemCode QtyOnHand
> 002-0200-10-WW-02 2000
> 002-0200-11-WW-02 1400
> 002-0200-12-WW-02 1500
> 010-0300-16-ED-03 1000
> 010-0300-16-MK-01 1000
> 010-0300-16-TR-02 1000
> I need the following (considering that the style = first 3 digits of the
> item codes)
> Style QtyOnHand JanOrders FebOrders Mar
> 002 4900 250 150
0
> 010 3000 300 0
> 100
> Thank you very much for your help so far...
> Rgds,
> "John Bell" wrote:
>

Friday, February 17, 2012

Extracting Contents of Object Variable in script Task

Hey Guys,

Im pretty new to Scripting in SSIS, but i have worked a fair bit out already, although i am stuck with the following problem.. If anyone can help out that would be great!!

Ok, i have a package that loops through records in a table, and extracts the detailed lines in a For Each loop. and stores them in a variable called DetailRecordSet with an data type of object.

I have a built a script task so that i can send out an email confirmation for each order, and i want to list the details in a HTML Table in the body of the message ( I have read that the standard send mail task wont support this, but found an example of using .NET code to generate the email message which will support it) .

What i want to know is how do i reference the columns in the Object variable so i can extract each line and add it into my string that i am creating with HTML codes.. I

e:( str = "<TABLE> <TR>" + Cstr(Variable for 1st column in recordset) + " </TR>"

if any can help my, that would be most appriciated.. Ive tried all different things that ive found on the net, but nothing is working.

Thanks in Advance

Scotty

To know how to interact with SSIS object variable's underlying type, you need to know the type itself. That is, the type of the "thing" stored by reference in the SSIS object variable.

What the actual underlying type of the variable stored in the SSIS variable of type object?

System.Windows.Forms.MessageBox.Show(Dts.Variables("DetailRecordSet").Value.GetType().FullName)

The above MessageBox() will display the underlying type in your script task, excepting Com callable wrappers (of type System.__Object).
|||

Sorry for the late reply Jaegd. but have been away for a few days,,

This object type is System.Object. from the variable section i select the data type to be Object.

I appricate your help still, cause i am still stuck

Thanks

Scotty

|||

As per the previous reply, knowing what is in the object is crucial. From your reply it is a COM object, so I'll guess that it is probably an ADO recordset. IN which case take a look here for some links and pointers - http://blogs.conchango.com/jamiethomson/archive/2005/02/08/SSIS_3A00_-Accessing-a-recordset-in-a-script-task.aspx

If that does not help, then help me, as I don't understand how you got there. So what has populated the variable exactly, and what method was used? If it is the For Each loop, what enumerator have you used?

Monday, March 19, 2012

Failed in installation, ERROR:29503

Monday, March 12, 2012

Failed in installatio, ERROR:29503

Wednesday, March 7, 2012

Fact and Dimension linking

Sunday, February 19, 2012

extracting results of XML stored proc to file

Extracting from linked tables into pivot

Friday, February 17, 2012

Extracting Contents of Object Variable in script Task

Extracting Sql

Blog Archive

About Me