Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked |||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked |||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>>I'm trying to copy objects and data between 2 SQL Server databases using
> the
>>import/export wizard in SQL Server 2000.
>>I select the source and destination database and leave everything as
>>default. When I try to run the DTS Package, it stalls at 22% for a few
>>minutes, then goes up to 96% or so very fast and gives the following
> message
>>quoted in the subject. ("Failed to copy objects...")
>>When I double-click the error, it reads the following:
>>"Need to run the object to perform this operation
>>[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
>>I'm running the package as "sa", so I don't think I don't have privileges
> to
>>anything.
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> > Are the two databases on the same machine? The same instance?
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>sql

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using t
he
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following messa
ge
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using
a
> Domain Admin account and also use Windows authentication when promted by t
he
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed Assertion Logmgr.cpp -- help!

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 11/1/2004
Time: 9:19:23 PM
User: HENT\Administrator
Computer: HESV0105
Description:
17066 :
SQL Server Assertion: File: <"logmgr.cpp">, line=2637
Failed Assertion = '!freeLC->lc_flushed'.
Data:
0000: aa 42 00 00 10 00 00 00 aB.....
0008: 09 00 00 00 48 00 45 00 ...H.E.
0010: 53 00 56 00 30 00 31 00 S.V.0.1.
0018: 30 00 35 00 00 00 0f 00 0.5....
0020: 00 00 49 00 4e 00 53 00 ..I.N.S.
0028: 49 00 47 00 48 00 54 00 I.G.H.T.
0030: 5f 00 44 00 42 00 5f 00 _.D.B._.
0038: 56 00 33 00 32 00 00 00 V.3.2...
We are running SQL 2000 SP3 on Windows 2000 Server SP4. The server is NOT a
domain controller. During the time of the error, there were no database
maintenance plans scheduled to run. Veritas BackUp exec is running on
another server, but it is done with SQL by 6:00pm, the error always happens
after that. There are no DTS packages running, either
This error happens occasionally, roughly 2-3 times per month. When it
happens, SQL maxes out processor utilization and eats up the pagefile.
Server is unresponsive, and the only course of action is to reboot the
server.
I've looked everywhere, support.microsoft.com, www.experts-exchange.com,
google, and many many newsgroups. Has anyone encountered this?
The database it fails on changes, this time it was HP Insight Manager, last
time it was our PACE replica database.Hi Kevin,
Your best bet is goign to be to contact Microsoft CSS - they can help you ge
t to the bottom of this.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Kevin Anderson" <Kevin Anderson@.discussions.microsoft.com> wrote in message
news:700E664C-667C-4C82-A08E-81E8647598F9@.microsoft.com...
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 11/1/2004
Time: 9:19:23 PM
User: HENT\Administrator
Computer: HESV0105
Description:
17066 :
SQL Server Assertion: File: <"logmgr.cpp">, line=2637
Failed Assertion = '!freeLC->lc_flushed'.
Data:
0000: aa 42 00 00 10 00 00 00 aB.....
0008: 09 00 00 00 48 00 45 00 ...H.E.
0010: 53 00 56 00 30 00 31 00 S.V.0.1.
0018: 30 00 35 00 00 00 0f 00 0.5....
0020: 00 00 49 00 4e 00 53 00 ..I.N.S.
0028: 49 00 47 00 48 00 54 00 I.G.H.T.
0030: 5f 00 44 00 42 00 5f 00 _.D.B._.
0038: 56 00 33 00 32 00 00 00 V.3.2...
We are running SQL 2000 SP3 on Windows 2000 Server SP4. The server is NOT a
domain controller. During the time of the error, there were no database
maintenance plans scheduled to run. Veritas BackUp exec is running on
another server, but it is done with SQL by 6:00pm, the error always happens
after that. There are no DTS packages running, either
This error happens occasionally, roughly 2-3 times per month. When it
happens, SQL maxes out processor utilization and eats up the pagefile.
Server is unresponsive, and the only course of action is to reboot the
server.
I've looked everywhere, support.microsoft.com, www.experts-exchange.com,
google, and many many newsgroups. Has anyone encountered this?
The database it fails on changes, this time it was HP Insight Manager, last
time it was our PACE replica database.

Fail to start Analysis Services Engine

Why I am not able to start the Analysis Services Engine again as the error message from event viewer as:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 0
Date: 04/07/2007
Time: 10:45:48
User: N/A
Computer: computer_name
Description:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: The filename, directory name, or volume label syntax is incorrect. . File system error: Error occurred during the creation of directory: '\\?\'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

On SQL Server, when I start the Analysis services, the error message is : The SQL Server Analysis Services (MSSQLSERVER) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and Alerts service.

Why is that? That is really frustrated and please any of you give me any advices or help on it. Thanks a lot in advance.

With best regards,

Yours sincerely,

I'm facing the similar problem as well. Fed up with SSAS 2005 on vista. Xp works fine.

I attempt to start my analysis serivces and it just failed. A check in the
Event Viewer --> Application log has uncover the below error detail.
Further googled and found 3 suggested solutions
1. Upgrade to Vista Ultimate. --> Doesn't work for me since i'm using Vista
Ultimate
2. Change locale to English (US) --> Doesn;t work as well. Same error
message.
3. Delete the folder MsSql.2 --> Doesn't work. Same error message.

Is this a bug in SSAS2005? Any more remedy? Hi Microsoft Guys, can you
help?

This is the detailed of the error:-

Log Name: Application
Source: MSSQLServerOLAPService
Date: 7/6/2007 12:04:13 AM
Event ID: 0
Task Category: (289)
Level: Error
Keywords: Classic
User: N/A
Computer: MyVista
Description:
The service cannot be started: Message-handling subsystem: The message
manager for the default locale cannot be found. The locale will be changed
to US English. Errors in the metadata manager. LOG file extension can be
only .LOG. Message-handling subsystem: The message manager for the default
locale cannot be found. The locale will be changed to US English.
Message-handling subsystem: The message manager for the 17417 locale cannot
be found. Internal error: Failed to generate a hash string.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLServerOLAPService" />
<EventID Qualifiers="49441">0</EventID>
<Level>2</Level>
<Task>289</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2007-07-05T16:04:13.000Z" />
<EventRecordID>15488</EventRecordID>
<Channel>Application</Channel>
<Computer>MyVista</Computer>
<Security />
</System>
<EventData>
<Data>Message-handling subsystem: The message manager for the default
locale cannot be found. The locale will be changed to US English. Errors in
the metadata manager. LOG file extension can be only .LOG. Message-handling
subsystem: The message manager for the default locale cannot be found. The
locale will be changed to US English. Message-handling subsystem: The
message manager for the 17417 locale cannot be found. Internal error: Failed
to generate a hash string.</Data>
</EventData>
</Event>

|||

Found the solution. Change the service for SSAS to run under different
account with EN-US locale. LSA was configured to use EN-MY locale.

If you insists to use LSA account. Start your registry editor. Goto
HKEY_USERS
look for LSA entry @. S-1-5-18
Search for Locale
Change
Locale = 00000409
LocaleName = en-US
sCountry = United States

Done. You can start your SSAS 2005 now

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Facxt Table Example

I need an example that shows how to uses multiple lookups to populate a fact table. The flow goes lilke this...

1. Read a staging table source that has source keys

2. for each source key, perform a lookup on the dimension table and return the surrogate key

3. Insert rows into the fact table with the surrogate keys

This is a standard approach that I've done many times in other ETL tools. However, I can't find any examples on how to get it to work. I have tried stringing the lookups together sequentially and using a multicast to peform the lookups in parallel. Neither approach would work. I could not find any examples on the web or in the SQL2005 samples.

Thanks,

Chris Busch

Blueprint Database

cbusch@.blueprintdatabase.com

Take a look at the samples you can download here:

http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm

This book is what I'd categorize as a "must have" if you're going to be spending much time with the MS BI stack.

Fact Table SQL Query - adding a prefix to a value

Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).

The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...

Is there a SQL function that allows me to

Example datasets:

Code Snippet

ConsNo DelNNo Canc Turnover Amount

001 200 0 1000 500

002 200 1 1000 500

I'd like to achieve this output by a query:

Code Snippet

ConsNo DelNNo Turnover Amount

001 200 1000 500

002 200 -1000 -500

Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...

Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?
|||

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

|||

Tiago Rente wrote:

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!

Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.

|||

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

|||

Tiago Rente wrote:

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...

|||

Summing the case statements should do the trick.

Code Snippet

select ConsNo,

DelNNo,

Turnover = SUM(case

when Canc = 0

then Turnover

else Turnover * -1

end),

Amount = SUM(case

when Canc = 0

then Amount

else Amount * -1

end)

from <table name>

GROUP BY

ConsNo

, DelNo

Fact Dimension Relationships on Partitioned Cube

Hi,

I have Fact Dimension Relationship on my cube, my concern is that the source of the dimension (Invoice Number) is just the 1st Partition of the cube. Should I only use a single partition on my cube to use the Fact Dimension Relationship?

What is the best practice to implement a Fact Dimension Relationship on a partitioned cube?

Thanks,

Renold

If you decided to build your fact dimension based on the same table as your partitions are, you should create a view joining all paritition tables and base your fact dimension off that view.

Otherwise the fact dimension is only going to show the invoice numbers that appear only in the first partition table.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Extracting data from one DB into another

I frequently extract data from one database into another, so I can then anal
yse and sometimes change the data without affecting the source.
These queries take quite a while and I know that the main database needs som
e re-design to help this along, and this is happening too.
What I really wondered is which is the quickest way for me to do this? Is it
better for me to create the table and then insert the right data into it? O
r is a "select into" statement going to be more efficient? Is there another
way I haven't even thought
of yet?
Any help appriciated!Is it the same data/tables? If it is why not set up a dts package to do it?