Urgent - Need help with T-SQL statement pls

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 2005 Forums

Transact-SQL (2005)

Urgent - Need help with T-SQL statement pls

Author

Topic

Mohan Ruthirakotti
Starting Member

4 Posts

Posted - 2008-01-03 : 14:22:49

Hi,
I need some help with the T-SQL statement. Could you please help?

The issue is that I am trying to export a table to a TEXT file using SQL Enterprise Manager (via Export Data). One of my source table's column is defined as "description text (16)". When i tried to output to a text file using query to data transfer, i see an incomplete value of the column. I wanted to see the entire value of the column... Do i need to convert the column to some other datatype? It is not returning the entire value in my output file. Please help!

Thanks
-Mohan

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:38:31

What does SELECT MAX(DATALENGTH(description)) FROM Table

tell you...what number

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Mohan Ruthirakotti
Starting Member

4 Posts

Posted - 2008-01-03 : 15:08:42

ok, i ran the following query and it returned "7957". What next?

SELECT MAX(DATALENGTH(description)) FROM item

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 15:17:28

That means your text column can fit in a varchar(8000) data type..but not by much

Is your key an IDENTITY column (say yes)

EDIT: Oh, and you probably are going to need to files anyway. Where are you taking the data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Mohan Ruthirakotti
Starting Member

4 Posts

Posted - 2008-01-03 : 15:24:30

Is your key an IDENTITY column? YES

Where are you taking the data?

I am converting the data into a TEXT file.

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 15:50:51

How are you gonna convert 8000k of text into a text file?

Is this an MS Word Merge?

OK, Do this

CREATE a View of the columns you want from the table

When you get to the description column do this


CREATE TABLE myTable99(Col1 int IDENTITY(1,1), [description] text)
GO
 
INSERT INTO myTable99([description])
SELECT REPLICATE('x',8000) UNION ALL
SELECT REPLICATE('y',8000) UNION ALL
SELECT REPLICATE('z',8000)
GO

SELECT * FROM myTable99
GO

CREATE VIEW myView99
AS
   SELECT Col1
	, SUBSTRING(description,1,8000) AS description
     FROM myTable99
GO

SELECT * FROM myView99
GO

DECLARE @cmd varchar(8000)
SET @cmd = 'bcp northwind.dbo.myView99 OUT d:\myView99.txt -c -T -S<your server name>'
EXEC master..xp_cmdshell @cmd
GO

-- Go to the D Root and look at your text file...

DROP VIEW myView99
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 15:53:17

quote:
Originally posted by Mohan Ruthirakotti

Is your key an IDENTITY column? YES

Where are you taking the data?

I am converting the data into a TEXT file.

I was gonna separate the text with a key to a separate file, and then have you do a second one with the data

If you do it all in one like I suggest in the prev post, I don't know if bcp wil choke or not

You might want to separate them

But what kind of data is this? Who's gonna look at it?

EDIT: Or is this a school exercise, in which case I feel foolish, because even if you do it this way, it's probably not what they want

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Mohan Ruthirakotti
Starting Member

4 Posts

Posted - 2008-01-04 : 17:17:44

Thanks to all. It was a data issue. After fixing the special characters, the query returned just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources