@SQL varchar (7000) only selects 256 chars? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

@SQL varchar (7000) only selects 256 chars?

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-02-26 : 15:04:55

CREATE PROCEDURE sp_montlysalereport
@CustNum int,
@firstdate datetime,
@lastdate datetime

AS

declare @SQL as varchar(7000)
select @SQL = 'SELECT tt.TERM_ID, tb.DBA, tb.Address1, tt.Prod_code,
(cast(sum(dbo.fniscredit(tt.credit, tt.AMT)) as money) AS Retail, createdate as billdate, (cast(sum(dbo.fniscredit(tt.credit, tt.AMT)*(1-tt.RetailerMargin)) as money) AS WholeSale, tt.CreateDate, tb.legalname, @RptTitle as rptTitle FROM TblTRACKING tt INNER JOIN Billing tb ON (tb.ProdCode = tt.Prod_Code) AND (tt.TERM_ID = tb.TerminalID) WHERE tt.CreateDate between @FirstDate and @LastDate
and tt.Override = No and tt.reversaloverride = no and tt.prod_code not in ("DAF0", "TRF0") GROUP BY tt.TERM_ID, tb.DBA, tt.Prod_code, tb.Address1, tt.RetailerMargin, tt.CreateDate, billdate, tb.legalname HAVING left(tt.TERM_ID, 6) in (cast(@custnum) as varchar) and sum(dbo.fniscredit(tt.credit, tt.AMT)) <> 0 ORDER BY tb.legalname, tt.TERM_ID'

select @SQL -- added to troubleshoot

exec(@SQL)

I added the 'Select @SQL' to see what it was generating...

It only returns the first 256 characters in the string. Whats up with that?

I'm running SQL2k Standard edition.

Daniel
SQL Server DBA

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-26 : 15:16:42

Tools->Options-Results->Max Characters per column = 256

Change it to whatever you want.

-Chad

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-02-26 : 15:38:34

quote:

Tools->Options-Results->Max Characters per column = 256

Change it to whatever you want.

-Chad

Thanks I dont remember ever having to do that with SQL 7. That a new setting in the new Query Analyzer?

Daniel
SQL Server DBA

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-26 : 15:43:21

I think it was the same in 7.0, but don't remember for sure, and don't have 7.0 QA handy to test.

-Chad

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-02-26 : 16:03:42

quote:

I think it was the same in 7.0, but don't remember for sure, and don't have 7.0 QA handy to test.

-Chad

No problem... I got my SP working now. I wanted to output it so I could then edit it in QA and with that little trick I was able to get it working.

Thanks again.

Daniel
SQL Server DBA

lfmn
Posting Yak Master

141 Posts

Posted - 2002-03-01 : 16:20:22

Just FYI in SQL Server 7 it's Query/Current Connection Options, then the Advanced tab

SQL is useful if you don't know cursors :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources