nvarchar and output to flat file.

Hello,
Im outputting a sql table to a text file. One of the columns in this table
is an nvarchar(1200) but the result in the flat file is truncated to 254
chars....how do ensure that it outputs all chars up to 1200?
thxJohn,
which approach / utility are you using to outputting the sql table to a text
file?
AMB
"John Smith" wrote:

> Hello,
> Im outputting a sql table to a text file. One of the columns in this table
> is an nvarchar(1200) but the result in the flat file is truncated to 254
> chars....how do ensure that it outputs all chars up to 1200?
> thx
>
>|||I think I gave him a suitable answer on a separate post.
Barrysql

Number to words

hi
I want to know how convert number to words or words to number
for example. if i give input 100, i have to get the output "one hundred"
is there any built in function available. need solution immediately.
regards
nlakkaRun the attached script in PUBS database and see if that's what you want. I didn't have enough time to debug it, so if you find anything fishy, - just let me know.|||OK, it has bugs, so hang on...|||Alright, now it's good to go (I think). A little roughy, but good enough for check generation and similar stuff.|||I have tried running the script but it should even translate the cents and not
e.g. 50/100.

Good luck|||I have tried running the script but it should even translate the cents and not
e.g. 50/100.

Good luck
Hey, Why don't you try this link (http://sqlkit.com/blogs/rudra/archive/2006/04/10/104.aspx) ? Just make two part according to the position of the '.' decimal ,and add regional customization according to your choice.
I will add that functionality very soon ...
And use this logic to make it ...

set @.s='2344.34'
set @.part1=substring(@.s,1,(len(@.s)-3))
set @.part2=substring(@.s,(len(@.s)-1),2)

and pass it to the function. Hope u will do that easily ;)

Number formatting has me stumped

Hi, I dont seem to be able to output a number that has 3 decimal places.
The data source returns eg. 12.564 % and the output format on the report
is ##.###.
The output value though shows 12.56%, I want to show the figure above.
What am I missing or doing wrong? Is there a rounding switch or option I am
missing ?
Thanks in advance .Try this:
#,##0.000
returns:
50.123
0.000
15,050.123
15.050,123 (for example in german systems)
"PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I
> am
> missing ?
> Thanks in advance .|||BTW, more information on number format strings can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:Ow%23cGYqwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Try this:
> #,##0.000
> returns:
> 50.123
> 0.000
> 15,050.123
> 15.050,123 (for example in german systems)
> "PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
>> Hi, I dont seem to be able to output a number that has 3 decimal places.
>> The data source returns eg. 12.564 % and the output format on the report
>> is ##.###.
>> The output value though shows 12.56%, I want to show the figure above.
>> What am I missing or doing wrong? Is there a rounding switch or option I
>> am
>> missing ?
>> Thanks in advance .
>|||In the Textbox Properties under Format, select Custom.
Use "N3" for numbers, or "P3" for percentages. You will see the example
change to the format you are trying to get.
"PaulQld" wrote:
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I am
> missing ?
> Thanks in advance .

Number formatting

Here's a quickie - I have a stored procedure which returns a list of numbers.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,95000),1),LEN(Convert(varchar(12),Convert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend :)
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> > Here's a quickie - I have a stored procedure which returns a list of
> numbers.
> > I want the output formatted with commas so 95,000 instead of 95000 - how
> > would I do that?
> Do it at the client, not at the server.
>
>

Number formatting

Here's a quickie - I have a stored procedure which returns a list of numbers
.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,9
5000),1),LEN(Convert(varchar(12),Con
vert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:

> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> numbers.
> Do it at the client, not at the server.
>
>

Number conversion

Hi all!

I have a int-column that stores large numbers in my table and I want the output from a SELECT-statement to output it diffrent...

If the number is 6 100 000 The output should be 6.1 and 19 000 000 should be 19.1

I would also like the numbers to be rounded to the closest 100 000 (round(myInt, -5) right?)

thanks guys!Is this what your after

SELECT ROUND(Cast(Number as Decimal)/1E6,1)

Dave|||I'd suggest using:SELECT Cast(myColumn / 1e6 AS DECIMAL(6, 1))-PatP

NULL output when trying to script objects with SQL-DMO

The following statement SHOULD generate a script of the specified stored
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer