ForEach Loop utilisation

Hello,

ForEach Loop Item allow to make operations row per row.

How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Thanks !So, a few folks have blogged about this.

http://www.sqlis.com/default.aspx?59
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

I thought Jamie did, but a quick search turned up empty.
In any case, one of the best sources of information on SSIS is Jamie Thomson's blog.
http://blogs.conchango.com/jamiethomson/default.aspx

HTH|||

Coroebus wrote:

How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Can you elaborate on exactly what you want to do? "10 rows per 10 rows" isn't very descriptive.

-Jamie|||

I want to make a request to yahoo stock quotes.

In my database i have a list of quotes, and i want to get a page with a selection of quotes.

look this example :
http://fr.old.finance.yahoo.com/d/quotes.csv?s=SLB.PA&f=snl1d1t1c1ohgv&e=.txt

I can request up to 200 quotes maximum like this

http://fr.old.finance.yahoo.com/d/quotes.csv?s=ATO.PA,AF.PA,AC.PA,ADE.PA&f=snl1d1t1c1ohgv&e=.txt

In my package i want to be able to create those request with a defined list of quotes.

I hope you can understand my bad english...

thanks a lot

|||Your english is good, don't worry about that :)

You can read your list of quotes into an Object variable using 1 of 2 methods:
1) Use the Execute SQL Task or
2) Use a data-flow with a Recordset destination component.

Once there you can loop over it using the Foreach Loop's "Foreach ADO Enumerator" and put the stock into a variable. The variable can then be used in a property expression to build a URL ("http://finance.yahoo.com/q?s=" + User::VariableName) for the HTTP Connection Manager that will retrieve the stock quote from the Yahoo site.

I don't have an SSIS instance to hand so can't build a demo of this but if you're having trouble let me know and I'll see what I can do later.

In the meantime, this article at SQLIS.com explains the basic process that you need to go through here: http://www.sqlis.com/default.aspx?59

Hope this helps.

-Jamie|||Ok, but how can I make row with 200 quotes ?|||

Coroebus wrote:

Ok, but how can I make row with 200 quotes ?

I'm not quite sure I understand. Do you mean that instead of 200 rows with 1 quote in each you want 1 row containing the same 200 quotes? [In other words you want to pivot the data.]

-Jamie|||No, I have in a table a list of 2000 quotes. I had to treat it by 200 quotes Items, request yahoo with this kind of http request.
In a sense, my package schould do this :

1- Create the list of quotes
2- Create http request for the 200 first quotes
3- Treat the http file
4- Create http request for the 200 Next quotes
5- Treat the http file
6- ...

I can do it quotes per quotes but i think it is more efficient 200 per 200

Thanks a lot for your help|||So you want to send the 2000 stocks to Yahoo in batches of 200, is that correct?
And the 200 stocks are stored in a table, is that correct?

If so I would do the following:
1) Have a Foreach loop that pulls a batch of 200 out of the table. It does this using the new Yukon windowing functions (http://sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk). Each time around the loop it pulls out the next batch of 200 until there is none left.
2) Inside that Foreach Loop have another Foreach loop that loops over the 200 returned rows, sending the request through to Yahoo for each one.

Does that make sense? Have I understood you correctly?

-Jamie|||YES !!!!

I was sure you'll find wht i want !

thanks a lot|||No problem. Let us know how you get on. The windowing functions are right up there on my "favourite new features of SQL Server" list. Nowhere near SSIS of course

-Jamie|||

Sorry... Those functions looks nice but are not made to my problem...

I'm trying to resolve my problem with a transformation script to make a recordset containing rows in the correct format.

If you want, i'll send you the script

Nico

|||If you think it'll help. jamie.thomson[at]donotspamme.conchango.com

-Jamie

Foreach loop doesn't loop

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

I have narrowed this down to enabling of checkpoint logging.

I have 3 loops inside each other. This is the top level loop that doesn't get past the 2nd row in the recordset.

Not got a simple repro yet.

Force Row Level locking in SQLServer 2000 ?

Hi

Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.

Best regards

AarnoArska (aarno.autio@.bof.fi) writes:
> Is it possible to force row level locking in one or more tables in
> some database. We have some problems when SQL Server decides to choose
> page- or table-level locking.
> We are using SQL Server 2000.

You can add a locking hint

SELECT * FROM tbl (ROWLOCK) WHERE col = 32

However, SQL Server may disregard that hint if row locks are possible
to achieve.

You may need to review you indexing strategy. For instance, in the example
above, I would not expect the hint to help if there is no index on col.
SQL Server will have to scan the entire table, so a tablock is called for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FOR XML return as a Scalar

When returning a result as XML using FOR XML SQL Server 2005 is returning
serveral rows when the result is greater than 2036 in length. Each row is
breaking on this length. I would like FOR XML to return all the xml in a
singe row single column so that I can use a select scalar for the results.
Currently I have to use a data reader and a string builder. I'm hoping that
there is a way to control the size if the output for FOR XML so that I can d
o
a scalar read of any result.
Thanks,
TylerHi Tyler,
Please post your statement for us to take a look at.
Thanks,
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <Tyler Carver@.discussions.microsoft.com> wrote in message
news:DE8A3709-2E31-49F6-B30C-C93B4B1195E0@.microsoft.com...
> When returning a result as XML using FOR XML SQL Server 2005 is returning
> serveral rows when the result is greater than 2036 in length. Each row is
> breaking on this length. I would like FOR XML to return all the xml in a
> singe row single column so that I can use a select scalar for the results.
> Currently I have to use a data reader and a string builder. I'm hoping
> that
> there is a way to control the size if the output for FOR XML so that I can
> do
> a scalar read of any result.
> Thanks,
> Tyler|||In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
SELECT * FROM tbl FOR XML AUTO, TYPE
In SQL 2000, you cannot do this directly in Query Analyzer. FOR XML returns
an XML stream which can be retreived as single string only if you use an API
which supports a stream interface. Since Query Analyer uses ODBC, the values
will be munged to 2032 characters per row.
One alternative is to use XML EXPLICIT with the edge table ( need to know
the resultset upfront ). Another is to extract the data externally ( to an
app or flat file ) and stitch them back together to form single XML
document.
Anith|||"Tony Rogerson" wrote:
> Please post your statement for us to take a look at.
Here is a SQL statement that returns a large XML result:
SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
This returns serveral rows from the database that need to be concatenated.
I want to change the statement so that only one row one column is returned.
Tyler|||For test...
select name as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
So yours would be...
SELECT *
FROM Categories
FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
> "Tony Rogerson" wrote:
> Here is a SQL statement that returns a large XML result:
> SELECT *
> FROM Categories
> FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
> This returns serveral rows from the database that need to be concatenated.
> I want to change the statement so that only one row one column is
> returned.
> Tyler|||Are you using SSMS? Can you post the results of:
DECLARE @.xml AS XML
SET @.xml = ( SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE )
SELECT DATALENGTH( @.xml )
Are you getting an error?
Anith|||Sorry, you'll need a delimiter as well...
select name + ',' as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u3ACF6b%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> For test...
> select name as [text()]
> from sys.objects
> for xml path( '' ), root( 'sysobjects' ), type
> So yours would be...
> SELECT *
> FROM Categories
> FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
> news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
>|||"Anith Sen" wrote:
> In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
Thanks Anith, this is exactly what I was looking for. We had several sprocs
without the Type directive and the example I had happened to have it and was
therefore unknown to us, working.
Thanks, again.
Tyler

FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

FOR XML Output

How do I get an output from the query below (1) one
record at a time, (2) insert the output one row at a time
into a XMLType column of a table? In addition, how do I
assign the output to a variable before step (2) so that I
can manipulate the output?
select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
***Please disregard the new Path feature in Yukon
Thanks,
C TO
I presume you use SQL Server 2005 Express or Beta 2.
If you need to insert each customer info in XML format into a separate row
of another table you can do (I use FOR XML ..., TYPE for FOR XML to generate
XML type directly):
insert into your_table_with_xml_col
select
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
for xml path('Customer'), TYPE
)
FROM Customers
If you want all customers into an XML variable you can write:
declare @.x xml
set @.x=
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer'), TYPE
)
Does it answer your question?
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:867101c47843$77008040$a601280a@.phx.gbl...
> How do I get an output from the query below (1) one
> record at a time, (2) insert the output one row at a time
> into a XMLType column of a table? In addition, how do I
> assign the output to a variable before step (2) so that I
> can manipulate the output?
>
<skip/>
> Thanks,
> C TO
|||Dear Eugene,
Beautiful!!!!!
Thanks, thank, thanks!!!
TO

>--Original Message--
>I presume you use SQL Server 2005 Express or Beta 2.
>If you need to insert each customer info in XML format
into a separate row
>of another table you can do (I use FOR XML ..., TYPE for
FOR XML to generate
>XML type directly):
>insert into your_table_with_xml_col
>select
> (select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> for xml path('Customer'), TYPE
> )
>FROM Customers
>If you want all customers into an XML variable you can
write:
>declare @.x xml
>set @.x=
>(select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> FROM Customers
> FOR XML PATH('Customer'), TYPE
>)
>Does it answer your question?
>Regards,
>Eugene
>--
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>"C TO" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:867101c47843$77008040$a601280a@.phx.gbl...
time[vbcol=seagreen]
I
><skip/>
>
>.
>

FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
Bill
First, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>
|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>
|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>
|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
BillFirst, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For MS Development Team - SP2

Hello,
I am experiencing an issue with exporting to Excel - on some reports the
last row is missing. Is there a hot fix for this and/or is this addressed in
SP2 for Reporting Services?
TIA.
--
Chris HanleyMost likely:
http://www.ReportingServicesFAQ.com/ow.asp?SP2ForRS%3F
chanley54 wrote:
> Hello,
> I am experiencing an issue with exporting to Excel - on some reports the
> last row is missing. Is there a hot fix for this and/or is this addressed in
> SP2 for Reporting Services?
> TIA.

For Loop

I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.

DECLARE @.i INT
SET @.i = 1

WHILE EXISTS(SELECT 1 FROM #Test WHERE id > @.i)
BEGIN
SELECT * FROM #Test WHERE id = @.i
SET @.i = @.i + 1
END

|||

Thank you for the reply Sir. But in the while loop you are giving a select statement. I guess it will perform a table scan which may slow down the process. I am not sure whether I am right or not. If I am right, then is there any alternative condition which can be incorporated in the while loop?

Thanks

Subhojeet

|||Due to the fact that this is a temporary table and not a table variable you can add a index to the table to avoid scans. But keep in mind that in small tables table scans can be faster for the query optimizer than doing a index scan.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Could you please explain what you are trying to do in the loop? You seem to be concerned about the EXISTS query performance but you are doing row-by-row processing which will be slow anyway. If you describe your problem with sample schema/data and expected results it will be easier to suggest a set-based solution.

For Each Row Trigger

Hi,

Does anybody know how could I define a for each row trigger in sql Server 2005?

What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.

"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.

In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.

Any suggestion?.

Thanks in advance.

Cheers.

You could use an INSTEAD OF INSERT trigger, and then have the trigger only insert the values that match your criteria. I'm not sure how your table is set up, but something similar to the following:

CREATE TRIGGER CheckInsertON Test_Table2INSTEADOF INSERTASBEGININSERT INTO Test_Table2("Time", InsertNumber)SELECT "Time", InsertNumberFROM INSERTEDWHERENOT EXISTS(SELECT InsertNumberFROM Test_Table2WHERE "Time"BETWEEN i."Time" AND i."Time2")END

 
The above query will only insert the records that don't fall between another record.
If you just want the insert to fail, and not insert any records, you could just check if there are any matches, and then return an error if there are.

|||

Hi, thanks for the reply,

That makes sense, I am going to try it and I will get you back whether it works or not.

Cheers.

|||

Hi, well I have defined the trigger and I think that it fires but I get an error during the insertion:

Cannot insert explicit value for identity column in table 'Items' when IDENTITY_INSERT is set to OFF. The statement has been terminated.

I dont know how to set this property...,

my trigger is the following in case it could help you.

ALTER TRIGGERtrgCheckItemInsert

ONdbo.Items

INSTEAD OF INSERT

AS

BEGIN

INSERT INTOItems(itemID,itemName,rackID,itemNumberOfUnits,startU,endU,itemDesc)

SELECTi.itemID,i.itemName,i.rackID,i.itemNumberOfUnits,i.startU,i.endU,i.itemDesc

FROMINSERTED i

WHERE NOT EXISTS(

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

)

END

any suggestion?

Thanks

|||

Hi, this problem is solved, I just had to remove the itemID from the insert statement.

Thanks.

|||

Well, finally the trigger doesnt fire, I have put a RAISERROR ('Hello',1,1) and it is not working, I catch the exceptions while the insertion between a try catch block and normally when I get an error from the database like duplicate id for the primary key or whatever I can catch it, but the raise statement I cant.

How could I ensure that the trigger fires? because moreover I am still able to insert worong values.

Thanks.

|||

I tried putting RaiseError in the first line of the trigger, and then ran it from the SQL Management Studio, and the rest of the trigger still ran, so I'm not sure if it will throw an exception in .NET. You could place a ROLLBACK TRANSACTION as the last line in the trigger, or else just comment out the INSERT command, and then see if anything is inserted into the table. Either of those should stop the insert, so you could at least tell if the trigger is running.

My guess is that the trigger is firing, but something in the WHERE clause is never evaluating to True, so that the NOT EXISTS always returns true. To test that, you could take the SELECT statement that you have in the EXISTS function, and see what it returns when you replace all the i.Columns with values that should find duplicates. Then see if any records are found. I don't know what your table schema looks like, so I'm not sure, but a couple of possibilities from the select statement below.

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

I don't think you need the "Items.itemID != i.itemID" because i.itemID is an AutoIncrement field, so that will never evaluate to false (I don't even know if i.itemID has a value at this point). That line shouldn't change the outcome, it just isn't needed.

the line "i.endU > Racks.numberOfUnits" sounds like it is checking for a condition you want met for the insert (This is in a NOT EXISTS, so you should only have conditions you do NOT want met). If that evaluates to FALSE even though i.startU is between startU and endU, you still may not get any results. I'm guessing you either need an OR instead of an AND, or else just check for the start and end dates and get rid of that check.

I wonder if you could rewrite the select command as

SELECTstartU

FROMItems

WHEREItems.rackID = i.rackID

ANDi.startUBETWEENItems.startUANDItems.endU

|||

Thanks for the reply,

well finally I got the solution, the trigger was wrong at some conditions and finally I fixed it.

The problem now arises when I define the INSTEAD OF UPDATE TRIGGER, because when I raise the error I cant catch it at any event, I try to catch it within the event Grid_Row_Updating and it doesnt work, and the same in the Row_Updated event, so I get an unhandled exception.

Do you have an idea about that?.

Cheers.

|||

It depends on how you are using the DataBinding. Are you calling DataBind() explicitly, or are you binding the GridView to the DataSource in the aspx page? The DataBind() method is where you need the Try-Catch. If you can bind the data to the GridView in the Page_OnLoad, that should let you catch the exception. I don't know if you can catch the exception if you are setting the DataSource in the aspx page.

For each loop

Hi,

I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?

I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?

Please help me with the above problem as I am working on a demo.

Thank you!!

Here's how you do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx and there is a downloadable demo on there as well

The only difference is that in the example given here the recordset object variable is populated with an Execute SQL Task rather than the Recordset destination but thereafter the same applies.

You cannot use the DataReader destination by the way!

-Jamie

For deleting duplicate row?

Hi,

I want to delete duplicate row from a very big table. Actually this table is used by a SP, and it's a very important. Due to duplicate record entry it's falling. I use bellow method for discarding the dulicate record.

PLz tel me it's the most efficent way to this job or u have some other way

1. I drop the primary key

2. Then I let all the duplicate record came into the table

3. then I removed them by using Group by clause and setting rowcount(1 - group by count).

4. Put primary key back and update the statistics.

Code is

If Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
DROP INDEX AdjustmentTransactions.PrimaryKey

Insert into AdjustmentTransactions
(UrnABS, UrnBar, .................Description)
Select a.UrnAbs,
a.UrnBar,
a.TxnUrn.....................
a.Description
from TxnProcess a
where a.InsUpFlag = 'I'
and a.Processed = 'N'
and ASCII(b.TxnType) = 65

Set @.row_count=0
Declare dup_cursor cursor for
Select UrnBAR,TxnUrnBarPat,count(*) counts from AdjustmentTransactions
group by UrnBAR,TxnUrnBarPat having count(*) > 1
Open dup_cursor
Fetch next from dup_cursor into @.VUrnBAR,@.VTxnUrnBarPat,@.count

While (@.@.Fetch_Status = 0)
Begin
Select @.row_count=@.count-1
Set rowcount @.row_count
Delete from AdjustmentTransactions where UrnBAR=@.VUrnBAR and TxnUrnBarPat=@.VTxnUrnBarPat
Fetch next from dup_cursor into @.VUrnBAR,@.VTxnUrnBarPat,@.count
End
Set rowcount 0
Close dup_cursor
Deallocate dup_cursor
If not Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
CREATE UNIQUE INDEX [PrimaryKey] ON [dbo].[AdjustmentTransactions]([UrnBAR], [TxnUrnBarPat]) ON [PRIMARY]
Update Statistics AdjustmentTransactions

Thanks

Sandipan

Is it SQL Server 2000 or 2k5 ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

it's sql server 2000

Thanks

sandipan