Pre execute Failure on Look up task

Hi,

I have an OLE DB Source. The output of OLEDB source is connected to a look up task. The output of the lookup task goes into OLE DB Destination. When executed..

I am getting this error:

DTS.Pipeline: component "Lookup" (2834) failed the pre-execute phase and returned error code 0x8007000E.

Any idea why pre-execute phase failure occurs?

TIN,
Anand

0x8007000E is out of memory error. How big is the reference table? Is Lookup running in Full Cache mode? Can you try running it in Partial Cache mode?|||Hi,

You are probably right. I have a data flow task before this specific task which contains 15 Look Up Tasks. I am presuming that memory is not released when needed.

The reference table is very small. It contains around 220 records.

Also, I have 2 GB memory available on this machine.

-Anandsql

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.

Powerpoint-Reporting with SQL2005 Reporting Services

Hi All,
the SQL Server Business Intelligence Development Studio is not equipped with
an option to output powerpoint presentations.
we have to migrate an legacy application which has been coded in access-vba.
the vba code uses data within an MSSQL Database and a powerpoint template
(.pot) and the output is a powerpoint presentation with lots of graphs and
charts in it.
we are thinking about porting the vba code into a code module within the SQL
Server Business Intelligence Development Studio.
does anybody know if it would technically be possible to use plain vba code
(ported from the access application) within SQL Server Business Intelligence
Development Studio to fill a powerpoint template and thus to generate a
whole powerpoint presentation? (provided that this whole vba-magic is
working within the access-application)
if the above stated idea is complete rubbish then please let me know. if it
might be possible to use SQL Server Business Intelligence Development Studio
to achieve this aim, please let me also know which steps this should
involve.
thanks a lot in advance!
best regards, janYou'd need to convert from VBA to VB.net.. I would really reccomend
writing some of the core logic in DLLs or something; I dont think that
putting hundreds of pages in code inside of a report would make a lot
of sense.. more importantly; you don't want 100 different copies of
this code; that would make maintenance difficult.
Then you would just digest this code inside the report properties
section; there is a 'code' window.
Why do you use powerpoint; anyways?
by chance do you work for a large auction web company in the Bay Area?
LoL?
We use OfficeWriter by SoftArtisans.. If I were you; I would push them
HARD to see if they can come up with a solution and then stand on the
shoulders of giants.
I just think that exporting to PPT might be a major pain.
Now.. for example.. if you wanted to talk about something that might be
a LOT LOT LOT easier to deal with?
you probably could just export the reports to XML format; and then
maybe make an XSLT that would 'translate' the XML out of SSRS into the
XML for PPT 07 or even 03.
Hope that helps; SSRS rocks!
-Aaron
Jan wrote:
> Hi All,
> the SQL Server Business Intelligence Development Studio is not equipped with
> an option to output powerpoint presentations.
> we have to migrate an legacy application which has been coded in access-vba.
> the vba code uses data within an MSSQL Database and a powerpoint template
> (.pot) and the output is a powerpoint presentation with lots of graphs and
> charts in it.
> we are thinking about porting the vba code into a code module within the SQL
> Server Business Intelligence Development Studio.
> does anybody know if it would technically be possible to use plain vba code
> (ported from the access application) within SQL Server Business Intelligence
> Development Studio to fill a powerpoint template and thus to generate a
> whole powerpoint presentation? (provided that this whole vba-magic is
> working within the access-application)
> if the above stated idea is complete rubbish then please let me know. if it
> might be possible to use SQL Server Business Intelligence Development Studio
> to achieve this aim, please let me also know which steps this should
> involve.
> thanks a lot in advance!
> best regards, jansql

Possible?: Count(*) returned by EXEC

Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@.ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@.ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @.ProductName

Any way to achieve this?

Thanks all

--James"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.posting.google.c om...
> Hi all,
> I have a stored procdure which does a select and returns the records
> directly -i.e. Not in output parameters e.g:
> CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
> SELECT *
> FROM MyTable
> WHERE [Name]=@.ProductName
> In another stored procedure I need to do the following:
> SELECT COUNT(*)
> FROM MyTable
> WHERE [Name]=@.ProductName
> As the select queries are actually a lot more complex that this, I'd
> rather not duplicate the select code in 2 sp's to save the maintenance
> effort - I'm looking for a way to execute the first procedure from the
> second and just count the records returned - something like:
> SELECT Count(*)
> FROM EXEC up_SelectRecs @.ProductName
> Any way to achieve this?
> Thanks all
> --James

See here:

http://www.sommarskog.se/share_data.html

If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:

select * from dbo.MyFunc(@.ProductName)
select count(*) from dbo.MyFunc(@.ProductName)

Simon

possible to select top 5 * of 2 or more individual criteria?

Hello,
I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
but in the same output I also want to include
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
I have thousands of rows where fldx starts with 'T' and 'S'. Is it possible
to select the desired rows above in the same output? what is the tsql for
this?
Thanks,
RichTry
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
UNION ALL
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
Keep in mind that the top clause doesn't have much meaning without an ORDER
BY, unless you are relying on the automatic ordering done on a table's
primary key.
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||use union all.
btw, what does "top 5" mean without an "order by" clause? also, "where fldx
like 'T%'" would much likely produce a more efficient exec plan than the
substring function on the column.
dean
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||SELECT * FROM
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'T'
ORDER BY '
) T
UNION ALL
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'S'
ORDER BY '
) S
ORDER BY '
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||Thank you all for your replies. I was working with substring earlier on
picking out 3 letters from a word, so that stuck in my brain. And I forgot
about including Order By for Top clause, and I was not even thinking about
Union All.
Thanks all for your help.
Rich
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ez$bhXSIGHA.1180@.TK2MSFTNGP09.phx.gbl...
> SELECT * FROM
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'T'
> ORDER BY '
> ) T
> UNION ALL
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'S'
> ORDER BY '
> ) S
> ORDER BY '
Have you done a search for SELECT * in 2005 bol?
:)|||Do 800 hits make it a good practice? I don't think so.
ML
http://milambda.blogspot.com/|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B77D3202-71FC-412D-A0BB-E8EEE669A599@.microsoft.com...
> Do 800 hits make it a good practice? I don't think so.
Perhaps the next time you advocate someone reading BOL
to get an intro to sql server you should use a asterick :)