XML Bulk Load: text

Showing posts with label text. Show all posts

Wednesday, March 28, 2012

Need to acess a field in a dataset from the other dataset.

This is a multi-part message in MIME format.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the reports. =
In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying = Web services through the XML data provider).So I am able to get the data = into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use = fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter = @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, = "DecryptedAddressDS")) but getting error as "Fields cannot be used in = query parameter expressions".
Are there other ways to acess a field in a dataset from the other = dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and 700+ = release cycles experience, we deliver unmatched value through high = quality, faster time to market and lower total costs.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I am using MS SQL Server 2005 Reporting = Services to develop the reports.
In my report I will be using 2 datasets = say A and B.
Dataset A connects to the XML type Data = Source Provider(i.e. querying Web services through the XML data provider).So I = am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server = Provider. Now I want to use fields from the dataset A into the query of the = dataset B.
I tried to access the fields of the = dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but getting = error as "Fields cannot be used in query parameter expressions".

Are there other ways to acess a = field in a dataset from the other dataset.

Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 2226900(Off) = Extn: 7026
Mob : 099605 93699www.persistentsys.com = Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development = services. With over 15 years, 140 customers, and 700+ release cycles experience, = we deliver unmatched value through high quality, faster time to market and = lower total costs.

--=_NextPart_000_0017_01C74FB3.C419E480--This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying =Web services through the XML data provider).So I am able to get the data =into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value (in =which case you can use first) you cannot do this the way you want. However, if =I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.
-- Bruce Loehle-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,

I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data provider).So =I am able to get the data into the dataset A from the XML =webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query of the =dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".

Are there other ways to acess a =field in a dataset from the other dataset.

Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com =Persistent Systems -Software Development Partner for Competitive Advantage. = Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release =cycles experience, we deliver unmatched value through high quality, faster =time to market and lower total costs.

--=_NextPart_000_0110_01C74F57.382E3590--|||This is a multi-part message in MIME format.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from dataset A into the one query of =dataset B. Like the query given below
Select count(distinct ani) from callsfromaug where applicationid in =(3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and =@.datecreated >=3D @.currentDate
where @.Address and @.datecreated are fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:ee%23fEm4THHA.4632@.TK2MSFTNGP04.phx.gbl...
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. =querying Web services through the XML data provider).So I am able to get =the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from =dataset A into the one query of dataset B. Like the query given below =
Select count(distinct ani) from callsfromaug where applicationid in (3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and @.datecreated >=3D @.currentDate
where @.Address and @.datecreated are =fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" wrote in message news:ee%23fEm4THHA.=4632@.TK2MSFTNGP04.phx.gbl...
You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value =(in which case you can use first) you cannot do this the way you want. However, =if I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you =drag and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.

-- Bruce Loehle-CongerMVP =SQL Server Reporting Services

"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,

I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data =provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query =of the dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".

Are there other ways to acess =a field in a dataset from the other dataset.

Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and =700+ release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.

--=_NextPart_000_0011_01C7501F.49516DB0--

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right
|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

sql

Need textboxs in a table to show zeros if no record found - not a NoRows message

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right|||

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

Need text in Tital Case

Hey Friends...

In database I have description field in Uppar case but I want to display it in Title Case ( I tried ProperCase but its not working in Crystal report 8.5)

Like :

In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE

But in report I want like : Take Back Control Of Your Change

and I am using crystal report 8.5 so can anybody help me please

Thanks

-Jayesh Mendpara
jmendpara@.gmail.comyou didn't mentioned what database u r using

oracle supports INITCAP funtion

im sure equivalent, available in SQL Server.

so u can handle that at database level.|||Hello sraheem...

I have the same situation as this initial post. However my situation is not really connected to a db. The initial dollar amount in the example does come from a db however the conversion just takes place within the report itself. I have converted the numbers to text and need to get this in title case. Example:

($583.00 converted to - five hundred eighty-three and xx/100)

I need the conversion to read: Five Hundred Eighty-Three And xx/100

Any assistance you could provide would be greatly appreciated. I thank you in advance for your time!|||Create a formula for the field you want to display as such and use something like this for the formula:

ProperCase ({MyTableNameGoesHere.MyFieldNameGoesHere})|||Will this work in 8.5?|||I think we already established that it won't.
Relatively simple to implement in a formula though.|||Check out this link for info on how to do it in 8.5.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=B649460555EE48B11E10F040D88394B5?cmd=displayKC&docType=kc&externalId=c2011637&sliceId=&dialogID=360384&stateId=1%200%20356275

Monday, March 26, 2012

Need suggestions on text file parsing into database

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions?

Thanks!

Hi keggdirdle,

As far as I can see, we cannot make the database recognize tab delimited text files automatically.

Whatever technology you use (stored procedure, ssis, or bulk upload), they all depends on fixed format for data.

In this case, I don't think it is possible without your own code.

Monday, March 19, 2012

Need Select Query

Hi to all
I Need a sql Qery for Text files.
Fist I want to know will it be possible to select the All Filelds fromText file...?
I just thoght of like
Select * from MyTextFile.Txt
Please Help me.to my knowledge there isn't a way to do this via Transact SQL. If you have access to the xp_cmdshell stored procedure yo umight try something like:

Code:
------------------------------
master.dbo.xp_cmdshell 'type MyTextFile.Txt'
------------------------------

Of course you will need to fully qualify the path to the text file as SQL server would assume (based on the above) that MyTextFile.Txt is on drive C: of your SQL server.|||I just tried like this
master.dbo.xp_cmdshell 'type C:\Sample.Txt'

I got the message as follows

outpur
The system cannot find the file specified.
NULL

(2 row(s) affected)|||again, xp_cmdshell will look on your sql server for the file NOT your local PC. Try this and see what you get:

code:
------------------------------
master.dbo.xp_cmdshell 'dir'
master.dbo.xp_cmdshell 'type c:\boot.ini'
------------------------------|||Thanks a lot
It is workig fine..

Wednesday, March 7, 2012

Need line feed in ToolTip text - how?

Hello,

I am displaying a complex formula in a column header tool tip. The formula is generated in a stored procedure. (I do not enter the tool tip text directly in the column header expression.)

Within the stored procedure, how do I generate a string that contains a carriage return/line feed?

Thanks,

BCB

In you sproc insert

select 'here is the break' + char(10) + 'here is the next line' as text

In your tool tip insert

=First(Fields!text.Value)

|||

Thanks for the response, but I don't think your solution will work in my case. The expression that I use for the tooltip text is:

=First(Fields!COL_4.Value, "ToolTips")

You can see that the tooltip is coming from a "ToolTips" dataset. The dataset is produced by a stored procedure. I need to format the tooltip string within the stored procedure that will contain the line feed. I don't believe the Chr(10) would work within a sproc.

Can you think of another approach?

Thanks,

BCB

|||Using CHAR(10) works fine, at least in SQL Server. Your database may be different, but I'd guess it still has some way to embed special characters into string data.|||

You will have to use CHAR(10)+CHAR(13) for line feed and carriage return in your stored proc in SQL Server.

Shyam

|||

I misunderstood Harley Rider's correct response. Thanks to everyone for setting me straight.

BCB

Need ideas on how to implement this...

I was thinking of a stored procedure that would select the top 20 rows in a
table and count words on a text column, returning the top 'n' words in
descending order of occurrence - not counting some common words (like on,
in, at, the, for, from, now, etc...)
Any ideas or routine ready to use?
Thanks in advance!
Mauricio Freitas, Microsoft MVP Mobile Devices
[url]http://www.g

zone.co.nz[/url]
[url]http://www.g

mac.com[/url]
Bluetooth guides: [url]http://www.g

zone.co.nz/content.asp?contentid=449[/url]
G

zone Software Store: [url]http://www.g

zone.co.nz/store[/url]Hi Mauricio
As a starting point you may want to check out something like the following
function:
http://www.sommarskog.se/arrays-in-sql.html#iterative
John
"Mauricio Freitas [MVP]" wrote:

> I was thinking of a stored procedure that would select the top 20 rows in
a
> table and count words on a text column, returning the top 'n' words in
> descending order of occurrence - not counting some common words (like on,
> in, at, the, for, from, now, etc...)
> Any ideas or routine ready to use?
> Thanks in advance!
>
> --
> Mauricio Freitas, Microsoft MVP Mobile Devices
> [url]http://www.g

zone.co.nz[/url]
> [url]http://www.g

mac.com[/url]
> bluetooth guides: [url]http://www.g

zone.co.nz/content.asp?contentid=449[/url]
> G

zone Software Store: [url]http://www.g

zone.co.nz/store[/url]
>
>
>|||Thanks John, will look at it :)
Mauricio Freitas, Microsoft MVP Mobile Devices
[url]http://www.g

zone.co.nz[/url]
[url]http://www.g

mac.com[/url]
Bluetooth guides: [url]http://www.g

zone.co.nz/content.asp?contentid=449[/url]
G

zone Software Store: [url]http://www.g

zone.co.nz/store[/url]
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:14F892B7-011C-4C2F-BF92-C5C258358811@.microsoft.com...
> Hi Mauricio
> As a starting point you may want to check out something like the following
> function:
> http://www.sommarskog.se/arrays-in-sql.html#iterative
> John
> "Mauricio Freitas [MVP]" wrote:
>

Saturday, February 25, 2012

Need help: Openxml failed for datasize greater than 120k

Hi,
Can anybody help me on what is going wrong, please?
I tried to pass an XML document as text parameter in a stored procedure
where openxml is being used to insert data into temp tables. If I pass any
XML document over 120k of size it goes in a loop and does not return any
error message. The SQL server is 2000 standard edition with service pack 3
running on windows 2003 server and it has all the latest versions of MSXML
(version 2, 3 and 4. All with latest service packs).
However if I try to execute it in my laptop (win XP professional, MSDE 2000
with service pack 3), it works fine. I tried with 500k XML data and it can
process.
Am I missing anything?
thanks
-Asir
Message posted via http://www.webservertalk.comHi
I assume you are calling SQLXML which uses MSXML? In which case make sure
that you have download the latest service pack (sp3). Without DDL, code or
example data it is not possible diagnose this problem. Check the differences
in the structure of the small files compared to the larger ones. You may wan
t
to try posting to microsoft.public.sqlserver.xml to see if anyone there has
more to add.
John
"Asir Sikdar via webservertalk.com" wrote:

> Hi,
> Can anybody help me on what is going wrong, please?
> I tried to pass an XML document as text parameter in a stored procedure
> where openxml is being used to insert data into temp tables. If I pass any
> XML document over 120k of size it goes in a loop and does not return any
> error message. The SQL server is 2000 standard edition with service pack 3
> running on windows 2003 server and it has all the latest versions of MSXML
> (version 2, 3 and 4. All with latest service packs).
> However if I try to execute it in my laptop (win XP professional, MSDE 200
0
> with service pack 3), it works fine. I tried with 500k XML data and it can
> process.
> Am I missing anything?
> thanks
> -Asir
> --
> Message posted via http://www.webservertalk.com
>|||My Code block is given below: From front end, ASP.Net(C#) I passed the XML
document as text to the stored procedure.
CREATE PROCEDURE p_xmlinsert
@.RequestId As Int,
@.Data1 As Int,
@.doc As text
As
Begin
Declare @.idoc Int
Declare @.TranCount Int,
@.Msg Varchar(256)
Set @.Msg = 'OK'
Set @.TranCount = @.@.TRANCOUNT
Exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
Into #Tmp1
FROM OPENXML (@.idoc, '/Data/Invoice',2)
WITH
( InvoiceNumber VarChar(64) '@.Invoice_Number',
Data1 Int '@.Data1',
Adderess1 VarChar(32) '@.Adder1',
Adderess2 VarChar(32) '@.Adder2',
City VarChar(32) '@.City',
State VarChar(32) '@.State',
Zip VarChar(32) '@.Zip',
Date VarChar(32) '@.Date',
Total VarChar(32) '@.Total'
)
SELECT *
Into #Tmp2
FROM OPENXML (@.idoc, '/Data/Invoice/Event',2)
WITH
( InvoiceNumber VarChar(64) '../@.Invoice_Number',
OrderNumber VarChar(32) '@.Order_Number',
OrderDate DateTime '@.Order_Date',
ContactName VarChar(64) '@.Contact_Name',
ContactEmail VarChar(64) '@.Contact_Email',
ContactPhone VarChar(24) '@.Contact_Phone',
ContactAddr1 VarChar(128) '@.Contact_Addr1',
ContactAddr2 VarChar(128) '@.Contact_Addr2',
ContactCity VarChar(32) '@.Contact_City',
ContactState VarChar(2) '@.Contact_State',
ContactZip VarChar(5) '@.Contact_Zip',
)
SELECT *
Into #Tmp3
FROM OPENXML (@.idoc, '/Data/Invoice/Event/Order_Detail',2)
WITH
(InvoiceNumber VarChar(64) '../../@.Invoice_Number',
OrderNumber VarChar(32) '../@.Order_Number',
ItemName VarChar(64) '@.Item_Name',
ItemDesc VarChar(256) '@.Item_Desc',
Quantity Decimal(18,2) '@.Quantity',
UnitPrice Decimal(18,2) '@.Unit_Price',
OrderTax Decimal(18,2) '@.Order_Tax',
OrderTotal Decimal(18,2) '@.Order_Total'
)
EXECUTE sp_xml_removedocument @.idoc
--Now Add data
If @.TranCount = 0
Begin Tran
Insert Into table1
(
ReqId,
Data1,
InvoiceNumber,
UnitAddress1,
UnitAddress2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
InvoiceNetAmount,
InvoiceTaxAmount,
InvoiceTotalAmount,
CreatedDate
)
Select
@.RequestId,
Data1,
InvoiceNumber,
UnitAdder1,
UnitAdder2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
0.0,
0.0,
Total,
GetDate()
From #Tmp1
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert1 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into table2
(
Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddress1,
ContactAddress2,
ContactCity,
ContactState,
ContactZip,
)
Select A.Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddr1,
ContactAddr2,
ContactCity,
ContactState,
ContactZip,
From #Tmp2 A,
Table1 B
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert2 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into Table3
(
Table2Id,
ItemName,
ItemDescription,
Quantity,
UnitPrice,
SalesTax,
TotalPrice
)
Select B.Table2Id,
ItemName,
ItemDesc,
Quantity,
UnitPrice,
OrderTax,
OrderTotal
From #Tmp3 A,
Table1 B,
Table2 C
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
And A.OrderNumber = C.OrderNumber
And B.Table1Id = C.Table1Id
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert3 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Update TableQueue Set Status = 'Ready' Where ReqId = @.RequestId and Status
= 'Pending'
If @.@.error<>0
Begin
Set @.Msg = 'Error: Update failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
If @.TranCount = 0
Commit Transaction
Select 3
Return
End
GO
Thanks
-Asir
Message posted via http://www.webservertalk.com

Wednesday, March 28, 2012

Need to acess a field in a dataset from the other dataset.

Need textboxs in a table to show zeros if no record found - not a NoRows message

Need textboxs in a table to show zeros if no record found - not a NoRows message

Need text in Tital Case

Monday, March 26, 2012

Need suggestions on text file parsing into database

Monday, March 19, 2012

Need Select Query

Wednesday, March 7, 2012

Need line feed in ToolTip text - how?

Need ideas on how to implement this...

Saturday, February 25, 2012

Need help: Openxml failed for datasize greater than 120k

XML Bulk Load

Blog Archive

About Me