Right-justify a column on export to text

Hello, I have a column (AccountNo per the below create script) that displays
properly with leading zeroes to fill a 22-character column while in SQL.
However, when I use a DTS export to a standard text file with no
transformation, it left-justifies. Could someone pls advise how I can get i
t
to export in a fixed length file preserving the leading zeroes and
right-justified? Thanks, Pancho.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GVMOI2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GVMOI2]
GO
CREATE TABLE [dbo].[GVMOI2] (
[TranDateSold] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerID] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDNum] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicationCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountNo] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TraceNbr] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TellerID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchNo] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckReferenceNbr] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CheckNbr] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankNumber] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Remitter1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payee1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ThirdParty] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Denomination] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDNumber] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDIssueBy] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDOthers] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GOGenerally, the following expression should create the string with the value
right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
Anith|||Well, it is right-justifying but losing my leading zeroes. I wish for the
column to generate like this:
0000000000000915999999
0000000000009160000000
Our data has varying length. In the example above, our source data begins
with the 9 and we would like to have leading zeroes as needed to make the
column 22 characters wide. We want to export it to a text file and keep the
zeroes but also keep the right-justified format.
"Anith Sen" wrote:

> Generally, the following expression should create the string with the valu
e
> right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
> --
> Anith
>
>|||Instead of SPACE(22), use REPLICATE( '0', 22 ).
Anith|||"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> Well, it is right-justifying but losing my leading zeroes. I wish for the
> column to generate like this:
> 0000000000000915999999
> 0000000000009160000000
> Our data has varying length. In the example above, our source data begins
> with the 9 and we would like to have leading zeroes as needed to make the
> column 22 characters wide. We want to export it to a text file and keep
> the
> zeroes but also keep the right-justified format.
> "Anith Sen" wrote:
>
I use this to right justify and zero fill a string in one of my
applications. May not be the best solution but it is what I came up with
when faced with a similar problem.
SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
ISNULL(column, REPLICATE('0', 22))
Kevin|||Well, both approaches work to create the column and display leading zeroes,
right-justified. However, exporting to a flat file, the zeroes remain but i
t
is defaulting to left-justified. Is there something I need to set in DTS?
Using Kevin's script I got the column to create with leading zeroes, 22
displaying and right justified, but it created a varchar column width of
8000. I would like it to be 22 characters wide in the output file. In DTS
I
changed size to 22 and tried type varchar and char but both resulted in
left-justified output columns in the text file.
"Kevin Haugen" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> I use this to right justify and zero fill a string in one of my
> applications. May not be the best solution but it is what I came up with
> when faced with a similar problem.
> SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
> ISNULL(column, REPLICATE('0', 22))
> Kevin
>
>

Right to run jobs

Thanks for the help with moving jobs from one server to another.

Now that I have moved them, they now fail each and all. I have copied the script and saved them as vbs files and placed them in a folder on the server and ran them successfully. I think it has something to do with the rights, but I don't know what. Could someone shed some light on possible causes?

Thanks,
LeeCould you post the errormessage?|||When I go to history, this is what is shown in the bottom
The job failed. The Job was invoked by Schedule 16 (NewMeterSch). The last step to run was step 1 (CreateMeterWO).|||Open the Job History, click on 'Show step details'.

For sted id, you should have a 0 and 1, highlight step id 1 which the job step is run agent.

Post that message?

Lystra|||Thanks for the help with moving jobs from one server to another.

I have copied the script and saved them as vbs files and placed them in a folder on the server and ran them successfully. Thanks,
Lee
Lee ... are these DTS packages that you scripted out and recreated on another server? If so, you will have to edit them to create the proper connection to the new server, because the existing conection will point to the old server.|||No, these jobs are not running DTS, They are vbscript. However it does use a dsn connection to the data. I have changed the script to call the correct dsn. Just to see if I had any errors, I copied the data into notepad and saved it as a vbs app; placed it in a folder on the server and ran the vbs. it ran fine. It just does not from within Jobs.

set Recordset1 = CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "dsn=SmithData;uid=donkey;pwd=mule;"
Recordset1.Source ="SELECT otbl_o_d_ProductionInfo.LineNum, otbl_o_d_ProductionInfo.Dept, otbl_o_d_ProductionInfo.EquipType, Sum(otbl_o_d_ProductionInfo.Production) AS SumOfProduction, tbl_cmms_Mertering.PMType FROM otbl_o_d_ProductionInfo INNER JOIN tbl_cmms_Mertering ON otbl_o_d_ProductionInfo.EquipType = tbl_cmms_Mertering.EquipType AND otbl_o_d_ProductionInfo.EntryDate >= tbl_cmms_Mertering.DateOfChange AND otbl_o_d_ProductionInfo.LineNum= tbl_cmms_Mertering.LineNum AND otbl_o_d_ProductionInfo.EquipType=tbl_cmms_Merteri ng.EquipType WHERE tbl_cmms_Mertering.UD2=0 GROUP BY otbl_o_d_ProductionInfo.LineNum, otbl_o_d_ProductionInfo.Dept, otbl_o_d_ProductionInfo.EquipType, tbl_cmms_Mertering.PMType HAVING Sum(otbl_o_d_ProductionInfo.Production)>=Max(tbl_cmms_Mertering.lifecycle)*.98"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0|||Apparently my username does not have the rights to run ActiveX jobs. I found this out by having our admin who has the password to sa change the owner to sa and the jobs ran. My question is how/can I be given rights to run ActiveX without signing on as sa (our admin is not going to let that happen)? Or better yet, can someone help me understand how script the job in SQL.
What I am doing is looping through the results of the query that is wrapped in the vbscript and writing a record into another table (again the loop and insert statement is wrapped in vbscript).

While I can script loops fairly well in vbscipt, I am not experienced in SQL.

Thanks,
Lee

Returning First Day of Week

Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.droptable.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
David Portas

Returning First Day of Week

Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.sqlmonster.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
--
David Portas|||When I am working with dates I always stick in an auxillary calendar
table, this is very useful in quickly finding days of weeks etc.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Returning error codes from OSQL

Hi,
I'm using OSQL with the -i option to run an SQL script. I'm also using
-o to save the output to a file. I'm invoking this OSQL command via
C++ code and would like to get the return code from the SQL script
back to the c++ code in case an error condition occurs.
OSQL help indicates using RAISERROR with state of 127 to return error
codes to the parent process. I've tried that but have not been able to
do this successfully. Any examples that anybody may have would be very
helpful.
There is also the -b option but that only returns 0 or 1.
Any help in retrieving the error code from the SQL script back from
OSQL would be very much appreciated.
Thanks,
Roopa
Have you tried something like this. This example set @.RC to -100 if table
XXXX doesn't exist. If XXXX does exist then @.RC is equal to 0.
declare @.cmd varchar(1000)
declare @.rc int
set @.rc = 0
set @.cmd = 'osql -E -Sdohdbolypr01 -Q"exit(select top 1 * from xxxx)"'
exec @.rc=master.dbo.xp_cmdshell @.cmd
print @.rc
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"RT" <rotrived@.cisco.com> wrote in message
news:f9f22d93.0409211255.6492b029@.posting.google.c om...
> Hi,
> I'm using OSQL with the -i option to run an SQL script. I'm also using
> -o to save the output to a file. I'm invoking this OSQL command via
> C++ code and would like to get the return code from the SQL script
> back to the c++ code in case an error condition occurs.
> OSQL help indicates using RAISERROR with state of 127 to return error
> codes to the parent process. I've tried that but have not been able to
> do this successfully. Any examples that anybody may have would be very
> helpful.
> There is also the -b option but that only returns 0 or 1.
> Any help in retrieving the error code from the SQL script back from
> OSQL would be very much appreciated.
> Thanks,
> Roopa
|||This is not what I want to do.
I don't have a single command that I run thru osql. I have a bunch of
commands in an sql script which I'm invoking thru osql. The osql
command is not invoked via a batch file. Instead I call the SQLPrepare
and SQLExecute methods to actually execute the osql command which in
turn runs all the commands in the script file.
Thanks,
Roopa
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:<#8KmLPCoEHA.1776@.TK2MSFTNGP14.phx.gbl>...[vbcol=seagreen]
> Have you tried something like this. This example set @.RC to -100 if table
> XXXX doesn't exist. If XXXX does exist then @.RC is equal to 0.
> declare @.cmd varchar(1000)
> declare @.rc int
> set @.rc = 0
> set @.cmd = 'osql -E -Sdohdbolypr01 -Q"exit(select top 1 * from xxxx)"'
> exec @.rc=master.dbo.xp_cmdshell @.cmd
> print @.rc
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "RT" <rotrived@.cisco.com> wrote in message
> news:f9f22d93.0409211255.6492b029@.posting.google.c om...