Reached nvarchar(4000) limit in building SQL statement.

SQL Server 2000 SP3a
Im using a sproc to make a sql statement. The statement is built up and
assigned to @.SQL1 nvarchar(4000), then at the end of the sproc its runs:
exec sp_executesql @.SQL1
The problem is that I've reached the 4000 limit!! How do other people get
round this, bearing in mind that its full of inner joins with sub selects, s
o
I dont think I can split it out. And because the sproc can create a view
which is updateable, I can't turn those sub selects into views as this makes
the newly created view non-updateable.
How can I build up a SQL string that is bigger than 4000 please.Split it up into multiple char/varchar variables and do:
EXEC( @.var1 + @.var2 + ...+ @.varn )
Anith|||Off the top of my head, can you incrementally build this using temp
tables and/or table variables?
Maybe something like...
procedure MyProc
as
-- do something with #temp1
-- do something with #temp2
select * from #temp1
join #temp1 on #temp2.something = #temp1.something
go
Just a thought.
Bryce|||If that means I can link several statements, which each on their own don't
make sense, together and execute in one go that would be perfect!!!
I won't be able to check til Monday, so many thanks in advance, I was
getting REALLY worried that many hours of trying to get get a one sproc does
all approach was going to fall at the last hurdle.
I shall read some more on exec / sp_executesql as it sounds like more can be
done than I had assumed.
Many thanks!
"Anith Sen" wrote:

> Split it up into multiple char/varchar variables and do:
> EXEC( @.var1 + @.var2 + ...+ @.varn )
> --
> Anith
>
>

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson