loosing characters when inserting to a table

Question

Post reply

loosing characters when inserting to a table

mudbuddy11

Old Hand

Points: 306
More actions
April 9, 2005 at 6:38 am

#76273

I know people frown on dynamic sql, and if someone else has a better idea, please let me know, but here is the problem... I have a dynamic SQL statement in the following code @compileDaily is the statement. I can make the statement just fine, but when I go to insert that value into a table(into a column of varchar(4000), it truncates the command.
It seems to usually cut off about 300 characters, and I dont see why it wont work...
Thanks in advance for your help.... it seems like I shouldnt even be having problems
c

declare @compileDaily varchar(4000)
set @compileDaily='declare @todayDate varchar(20)
declare @useDate varchar(20)
set @todayDate=current_timestamp
set @todayDate=''1/1/05''
--print @todayDate
set @useDate=dateadd(d,-1,@todayDate)
--print @useDate
--the following must be run from SFTP database
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''dailySales'')-- " + vbCrLf + _
DROP TABLE dailySales
select * into dailySales from HTdb..sales_t where datediff(dayOfYear,Date_DT,@useDate)=0
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''dailyCustomers'')-- " + vbCrLf + _
DROP TABLE dailyCustomers
select * from HTdb..customers_t where datediff(dayOfYear,DateAddedToDB_DT,@useDate)=0'
print @compileDaily
drop table tester
create table tester(num integer null,
theText varchar(4000) null)
insert into tester select 1,@compileDaily
select * from tester

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

Are you absolutely sure you are loosing characters?

1. When you select from the table in the query analyzer are you using data table or textual output?

2. In the QA select Tools -> Options -> Results tab. What is the maximum characters per column set at?

3. Instead of vbCrLf have you tried using CHAR(13)?

Just some thoughts of what to look at.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Wayne West SSC-Insane Points: 22586 More actions · Answer 2

Out of curiosity, I'd alter the table definition and make the varchar(4000) field a text field and see if it behaves the same. I would also check the incoming data and make sure there isn't a Ctrl-Z character hidden in it. My brain is a bit fuzzy at the moment and I'm not really following entirely what your code is trying to do. I'm getting most of it, but my eyes hurt so I'm going to go away and do something else for a while. 🙂

Good luck!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 3

I tried you script, and the insert works fine.

Please verify if the option in QA ( Tools -> Options -> Results tab ) "Maximum characters per colomn" is set to a value that is big enough .... I suppose the data is OK in the tester table, but Query Analyer wont show you all the data because of the option ....

Bert

mudbuddy11 Old Hand Points: 306 More actions · Answer 4

Thanks everyone, I was being a dork.... not checking things thouroughly enough. I didnt see enough output in qa and freaked. I'm sending sql queries to a remote group of computers through tables (so they can be processed) This is a process that should pull out yesterday's sales.