Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mbyrd
Starting Member
5 Posts |
 Posted - 2007-07-20 : 09:29:59
|
| While running query below on SQL Server 2005 (Build 3790: Service Pack 2):SELECT DISTINCT pkg.PrimaryBarcode FROM dbo.Package AS pkg (NOLOCK) JOIN dbo.PackageCycle AS pc (NOLOCK) ON pkg.PackageKey = pc.PackageKey WHERE (pkg.BillCycleDateKey >= 20061201) OR (pkg.BillStatusKey = 1)I received a partial result set (should return about 10.5m rows) followed byAn error occurred while executing batch. Error message is: Couldn't replace textI suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.Cheers,Mike Byrd |
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-07-20 : 10:13:12
|
quote:
Originally posted by mbyrd
While running query below on SQL Server 2005 (Build 3790: Service Pack 2):SELECT DISTINCT pkg.PrimaryBarcode FROM dbo.Package AS pkg (NOLOCK) JOIN dbo.PackageCycle AS pc (NOLOCK) ON pkg.PackageKey = pc.PackageKey WHERE (pkg.BillCycleDateKey >= 20061201) OR (pkg.BillStatusKey = 1)I received a partial result set (should return about 10.5m rows) followed byAn error occurred while executing batch. Error message is: Couldn't replace textI suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.Cheers,Mike Byrd
10.5 million rows is a TON of data, and you could very well run out of memory running this. Do you absolutely need to return 10.5 million rows? |
 |
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-07-20 : 10:43:17
|
| Maybe a SELECT INTO, then work with the new table in smaller chunks? |
|
|
|
mbyrd
Starting Member
5 Posts |
Posted - 2007-07-20 : 10:48:18
|
| Acutally this query is from an ETL package (informatica) that is trying to populate a Load table. I've run the query in SSMS and received same error. I'm not sure how to get Informatica to break down the data load. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-20 : 10:59:38
|
| use bulk insert or SSIS for this._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
Error - Large Result Set