Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
jouj
use cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
|||See:
http://groups-beta.google.com/group/...d?dmode=source
Anith
|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
Sunday, February 26, 2012
Format Numbers
I have a report that returns a column with number values that I want to round
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>
Format Numbers
How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123
Format Numbers
Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/97af3e2b3f45b72d?dmode=source
--
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/97af3e2b3f45b72d?dmode=source
--
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
Format Numbers
Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
f3e2b3f45b72d?dmode=source" target="_blank">http://groups-beta.google.com/group...2d?dmode=source
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
f3e2b3f45b72d?dmode=source" target="_blank">http://groups-beta.google.com/group...2d?dmode=source
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
Format Numbers
How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123
format number with decimals
I need to know to format a field that has decimals.
If the decimal portion is .00 then I do not want to display it.
For example:
1234.01 would display as 1234.01
1234.00 would display as 1234
Any help would be great thanks.
Dean
I found the answer:
if int({field})= {field} then
formula = totext({field},"##########")
else
formula = totext({field},"##########.##")
end ifHi,
Try this
if right(Cstr({feild}),3)=".00" then
mid(cstr({feild}),1,len(cstr({feild}))-3)
else
cstr({feild});
Madhivanan
If the decimal portion is .00 then I do not want to display it.
For example:
1234.01 would display as 1234.01
1234.00 would display as 1234
Any help would be great thanks.
Dean
I found the answer:
if int({field})= {field} then
formula = totext({field},"##########")
else
formula = totext({field},"##########.##")
end ifHi,
Try this
if right(Cstr({feild}),3)=".00" then
mid(cstr({feild}),1,len(cstr({feild}))-3)
else
cstr({feild});
Madhivanan
Format number with 2 decimal places
I am having trouble formatting a DATEDIFF with 2 decimal places.
Can anyone help?
tbVisitLog
VisitID | ChildID | DateTimeIn | DateTimeOut | etc...
tbChild
ChildID | ChildLastName
When calculating duration of stay...
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the child") AS Amou
ntDue
FROM tbVisitLog v
INNER JOIN tbChild ON c.ChildID = v.ChildID
WHERE c.ChildID IN (group of children leaving)
I want to format the duration * rate to be a two-decimal number. Without for
matting I get numbers like this:
ChildName | 0.52000000000000002
ChildName | 4.0199999999999996
Thanks
Sonny
--
--Try this (untested):
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(cast(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the
child") as decimal(14,2)) AS AmountDue
FROM ...
ML
Can anyone help?
tbVisitLog
VisitID | ChildID | DateTimeIn | DateTimeOut | etc...
tbChild
ChildID | ChildLastName
When calculating duration of stay...
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the child") AS Amou
ntDue
FROM tbVisitLog v
INNER JOIN tbChild ON c.ChildID = v.ChildID
WHERE c.ChildID IN (group of children leaving)
I want to format the duration * rate to be a two-decimal number. Without for
matting I get numbers like this:
ChildName | 0.52000000000000002
ChildName | 4.0199999999999996
Thanks
Sonny
--
--Try this (untested):
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(cast(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the
child") as decimal(14,2)) AS AmountDue
FROM ...
ML
Format number in stored procedure
I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.
com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.
com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
Format number in stored procedure
I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
Format number
I am doing some simple arithmatic for a query that I am working on. What I have below describes the arithmatic that is taking place withing a portion of the query:
select(o.sales_val/o.qty_sales) as 'Unit_Price', (o.acctng_cost_val/o.qty_sales) as 'Unit_Cost'
from opcsahf as o
go
The division is correct. My only issue is that the results for 'Unit Price' or 'Unit Cost' may be formatted like example: 4.25000000.
How can I have my nubers show up with only to 2 decimal places instead of all the zeros at teh end?It really is a presentation layer issue..
What are the datatypes of the columns
Did you just try CONVERT(decimal(15,2),....|||Brett,
I went ahead and tryed the convert(decimal(15,2) and everything worked fine. This was the results for the statement:
select convert(decimal (15,2),o.sales_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Price', convert(decimal (15,2),o.acctng_cost_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Cost'
from opcsahf as o
go
Thanks alot Brett. It did the trick.
Originally posted by Brett Kaiser
It really is a presentation layer issue..
What are the datatypes of the columns
Did you just try CONVERT(decimal(15,2),....
select(o.sales_val/o.qty_sales) as 'Unit_Price', (o.acctng_cost_val/o.qty_sales) as 'Unit_Cost'
from opcsahf as o
go
The division is correct. My only issue is that the results for 'Unit Price' or 'Unit Cost' may be formatted like example: 4.25000000.
How can I have my nubers show up with only to 2 decimal places instead of all the zeros at teh end?It really is a presentation layer issue..
What are the datatypes of the columns
Did you just try CONVERT(decimal(15,2),....|||Brett,
I went ahead and tryed the convert(decimal(15,2) and everything worked fine. This was the results for the statement:
select convert(decimal (15,2),o.sales_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Price', convert(decimal (15,2),o.acctng_cost_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Cost'
from opcsahf as o
go
Thanks alot Brett. It did the trick.
Originally posted by Brett Kaiser
It really is a presentation layer issue..
What are the datatypes of the columns
Did you just try CONVERT(decimal(15,2),....
format Now()...
How do I format Now() to look like dd/MM/yyyy (without time part) as a
default value for a report parameter?Now.ToShortDateString()
or
FormatDateTime(Now(),vbShortDate)
depending on where and when you use it.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||You can use Today() instead of Now(), but that simply displays the full date
& time with the time at midnight.
I was able to get closer with this as the default:
=Format(Today(),"dd/MM/yyyy")
But it's producing a type mismatch error if the parameter is a DateTime.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||If you don't want the user to see the time then you need to change the
parameter type to string. Then what Jeff says below will work. There is no
way if you have set the parameter type to datetime for it to not show the
time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You can use Today() instead of Now(), but that simply displays the full
date
> & time with the time at midnight.
> I was able to get closer with this as the default:
> =Format(Today(),"dd/MM/yyyy")
> But it's producing a type mismatch error if the parameter is a DateTime.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
> > default value for a report parameter?
>|||Use =Today() and set the field format to d.
Patrick
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:e%23xSbL64EHA.1596@.tk2msftngp13.phx.gbl...
> If you don't want the user to see the time then you need to change the
> parameter type to string. Then what Jeff says below will work. There is no
> way if you have set the parameter type to datetime for it to not show the
> time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> You can use Today() instead of Now(), but that simply displays the full
> date
>> & time with the time at midnight.
>> I was able to get closer with this as the default:
>> =Format(Today(),"dd/MM/yyyy")
>> But it's producing a type mismatch error if the parameter is a DateTime.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
>> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
>> > default value for a report parameter?
>>
>
default value for a report parameter?Now.ToShortDateString()
or
FormatDateTime(Now(),vbShortDate)
depending on where and when you use it.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||You can use Today() instead of Now(), but that simply displays the full date
& time with the time at midnight.
I was able to get closer with this as the default:
=Format(Today(),"dd/MM/yyyy")
But it's producing a type mismatch error if the parameter is a DateTime.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||If you don't want the user to see the time then you need to change the
parameter type to string. Then what Jeff says below will work. There is no
way if you have set the parameter type to datetime for it to not show the
time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You can use Today() instead of Now(), but that simply displays the full
date
> & time with the time at midnight.
> I was able to get closer with this as the default:
> =Format(Today(),"dd/MM/yyyy")
> But it's producing a type mismatch error if the parameter is a DateTime.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
> > default value for a report parameter?
>|||Use =Today() and set the field format to d.
Patrick
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:e%23xSbL64EHA.1596@.tk2msftngp13.phx.gbl...
> If you don't want the user to see the time then you need to change the
> parameter type to string. Then what Jeff says below will work. There is no
> way if you have set the parameter type to datetime for it to not show the
> time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> You can use Today() instead of Now(), but that simply displays the full
> date
>> & time with the time at midnight.
>> I was able to get closer with this as the default:
>> =Format(Today(),"dd/MM/yyyy")
>> But it's producing a type mismatch error if the parameter is a DateTime.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
>> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
>> > default value for a report parameter?
>>
>
Format Negative Number
Does anyone know how I can use the format statement to show -234.00 as
(234.00)?Jeannie wrote:
> Does anyone know how I can use the format statement to show -234.00 as
> (234.00)?
Never mind - I didn't realize that a simple "C" would do it!
(234.00)?Jeannie wrote:
> Does anyone know how I can use the format statement to show -234.00 as
> (234.00)?
Never mind - I didn't realize that a simple "C" would do it!
format month name in chart
I'm trying to format the month name in a chart. I have dates, such as 1/31/2006, 2/28/2006 etc. I want them to display as Jan, Feb, Mar etc.
I know I can put 'P0' in the format field to format as a %.
Is there a format for the month name?
Yes, DateName(DatePart(month,"1/31/2006"))
Hammer
Format month
In my report i need to change format to E_Month field;
from this data '200502' change format to this '02/2005'.
How should i change that?
Thank you.How about
=Right(E_Month,2) & "/" & Left(E_Month,4)
?
Michael C
"GGill" wrote:
> In my report i need to change format to E_Month field;
> from this data '200502' change format to this '02/2005'.
>
> How should i change that?
> Thank you.|||Thanks.
It works.
"Michael C" wrote:
> How about
> =Right(E_Month,2) & "/" & Left(E_Month,4)
> ?
> Michael C
>
> "GGill" wrote:
> > In my report i need to change format to E_Month field;
> > from this data '200502' change format to this '02/2005'.
> >
> >
> > How should i change that?
> >
> > Thank you.
from this data '200502' change format to this '02/2005'.
How should i change that?
Thank you.How about
=Right(E_Month,2) & "/" & Left(E_Month,4)
?
Michael C
"GGill" wrote:
> In my report i need to change format to E_Month field;
> from this data '200502' change format to this '02/2005'.
>
> How should i change that?
> Thank you.|||Thanks.
It works.
"Michael C" wrote:
> How about
> =Right(E_Month,2) & "/" & Left(E_Month,4)
> ?
> Michael C
>
> "GGill" wrote:
> > In my report i need to change format to E_Month field;
> > from this data '200502' change format to this '02/2005'.
> >
> >
> > How should i change that?
> >
> > Thank you.
Format money value as padded string
Ok my last formatting question.
>
>
>
>
>
>
I had a similar project. I did this and it works great:
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
>
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
>
It looks like a bug to me.
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>>
>>Good question. Seems like the reailing spaces are stripped when the
>>string is passed to replace(). Probably, because there is a conversion
>>to varchar, but trailing spaces should be retained, as long as the
>>setting ANSI_PADDING is in effect.
>>
>>It looks like a bug to me.
>
>
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
>
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>
>
How can I insert a money value as a padded string in another table?
example $1.25 gets inserted to another table as 00000125
I want 8 total characters and no decimal
another example would be 4,225.99 becomes 00422599
can this be done?
thank you!!declare @.m money
set @.m = $1.25
select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 10:15:08 -0800, paulmac106@.gmail.com wrote:
Quote:
Originally Posted by
>Ok my last formatting question.
>
>How can I insert a money value as a padded string in another table?
>
>example $1.25 gets inserted to another table as 00000125
>
>I want 8 total characters and no decimal
>
>another example would be 4,225.99 becomes 00422599
>
>can this be done?
>
>thank you!!
Quote:
Originally Posted by
declare @.m money
set @.m = $1.25
>
select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)
>
Roy Harvey
Beacon Falls, CT
>
On 22 Feb 2007 10:15:08 -0800, paulmac...@.gmail.com wrote:
>
Quote:
Originally Posted by
Ok my last formatting question.
>
Quote:
Originally Posted by
How can I insert a money value as a padded string in another table?
>
Quote:
Originally Posted by
example $1.25 gets inserted to another table as 00000125
>
Quote:
Originally Posted by
I want 8 total characters and no decimal
>
Quote:
Originally Posted by
another example would be 4,225.99 becomes 00422599
>
Quote:
Originally Posted by
can this be done?
>
Quote:
Originally Posted by
thank you!!
I had a similar project. I did this and it works great:
REPLACE(REPLACE(CONVERT(char(8), @.m), '.', ''), ' ', '0')
-Utah|||thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
it just won't seem to put the zero in...very strange
i get this for 15: '15 '|||(paulmac106@.gmail.com) writes:
Quote:
Originally Posted by
thanks that worked great.
>
any idea why this doesn't work:
>
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>
it just won't seem to put the zero in...very strange
>
i get this for 15: '15 '
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
It looks like a bug to me.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i got it to work using:
RIGHT('0000'+REPLACE(SUM(tblLines.fldUnits), ' ', '0'), 4)
results in 0015 where SUM(tblLines.fldUnits)=15|||Erland Sommarskog (esquel@.sommarskog.se) writes:
Quote:
Originally Posted by
(paulmac106@.gmail.com) writes:
Quote:
Originally Posted by
>thanks that worked great.
>>
>any idea why this doesn't work:
>>
>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>
>it just won't seem to put the zero in...very strange
>>
>i get this for 15: '15 '
>
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
>
It looks like a bug to me.
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
I've submitted a workaround to this Feedback item. If you
concatenate the empty string '' to the CHAR value before
REPLACE is applied, the hidden conversion to VARCHAR
retains the trailing blanks.
declare @.t char(6)
set @.t = 'A'
select
replace(@.t,space(1),'*'),
replace(@.t+'',space(1),'*')
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- C70DF007-1034-489C-A71E-108FBC89D553
Erland Sommarskog wrote:
Quote:
Originally Posted by
Erland Sommarskog (esquel@.sommarskog.se) writes:
>
Quote:
Originally Posted by
>(paulmac106@.gmail.com) writes:
>>
Quote:
Originally Posted by
>>>thanks that worked great.
>>>
>>>any idea why this doesn't work:
>>>
>>>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>>
>>>it just won't seem to put the zero in...very strange
>>>
>>>i get this for 15: '15 '
>>
>>Good question. Seems like the reailing spaces are stripped when the
>>string is passed to replace(). Probably, because there is a conversion
>>to varchar, but trailing spaces should be retained, as long as the
>>setting ANSI_PADDING is in effect.
>>
>>It looks like a bug to me.
>
>
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
>
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>
>
Format money MS SQL field data as number with commas and no decimals
If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals?
87000.0000 = 87,000
I can currently remove the decimals like below but is there a way to add the commas as well?
decRevenue = drMyData("Revenue")
txtRevenue.Text = decRevenue.ToString("f0")
It current shows "87000".
http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.80).aspx
Format measure from milliseconds to HH:MM:SS
Hi,
I have a measure that contains milliseconds, I want to display it in the HH:MM:SS format.
How to specify the format string?
please help.
Regards
Vijay R
Hi Vijay,
This thread from the public SQL Server OLAP Newgroup discusses some approaches:
http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/37b9ae771bc762bc
>>
This MSDN link discusses FORMAT_STRING options for cube measures:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdma
d/agmdxadvanced_2aur.asp
This earlier post shows how to format hh:mm:ss
http://groups-beta.google.com/group/microsoft.public.sqlserver.olap/msg/
13d3af02580a7257
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdma
d/agmdxadvanced_2aur.asp
This earlier post shows how to format hh:mm:ss
http://groups-beta.google.com/group/microsoft.public.sqlserver.olap/msg/
13d3af02580a7257
From: Deepak Puri (d...@.progressive.com)
Subject: Time format Measure
This is the only article in this thread
View: Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-04-24 21:53:31 PST
Try the following format, if time is < 32K seconds:
With Member [Measures].[FormattedASA] as
'TimeSerial(0,0,[Measures].[AvgSpeedAnswer])',
FORMAT_STRING = 'hh:nn:ss'
>>
Format matrix subtotal column
I have a matrix that has columns for each quarter of the year and I added a
subtotal colum for the year total. I would like to have the data cells in the
subtotal column be a different color than that of the the quarter columns. I
thought that using the little green triangle in the subtotal cell would work,
but it doesn't. How can I do this?
Thanks...
Regards,
SteveIt is the little green triangle - BackgroundColor in Subtotal properties.
You also have to assign a BackgroundColor for the textbox that is your column
heading.
Do you have some expression overriding it?
daw
"Steve" wrote:
> I have a matrix that has columns for each quarter of the year and I added a
> subtotal colum for the year total. I would like to have the data cells in the
> subtotal column be a different color than that of the the quarter columns. I
> thought that using the little green triangle in the subtotal cell would work,
> but it doesn't. How can I do this?
> Thanks...
> Regards,
> Steve|||For some reason I can't get it to work on a column subtotal, but it works
fine for a row subtotal. No expressions overriding it far as I can see.
The BackgroundColor for the column heading is LightSkyBlue, but changing the
BackgroundColor on the subtotals textbox to, say Red, using the little green
arrow has no effect.
I was able to get it to work by setting the BackgroundColor property in the
data cell, i.e. the one with the sum in it, using this expression:
=iif(InScope("matrix3_ColumnGroup2"),"Quarter totals","Year totals")
Thanks,
Steve
"daw" wrote:
> It is the little green triangle - BackgroundColor in Subtotal properties.
> You also have to assign a BackgroundColor for the textbox that is your column
> heading.
> Do you have some expression overriding it?
> daw
> "Steve" wrote:
> > I have a matrix that has columns for each quarter of the year and I added a
> > subtotal colum for the year total. I would like to have the data cells in the
> > subtotal column be a different color than that of the the quarter columns. I
> > thought that using the little green triangle in the subtotal cell would work,
> > but it doesn't. How can I do this?
> >
> > Thanks...
> >
> > Regards,
> > Steve
subtotal colum for the year total. I would like to have the data cells in the
subtotal column be a different color than that of the the quarter columns. I
thought that using the little green triangle in the subtotal cell would work,
but it doesn't. How can I do this?
Thanks...
Regards,
SteveIt is the little green triangle - BackgroundColor in Subtotal properties.
You also have to assign a BackgroundColor for the textbox that is your column
heading.
Do you have some expression overriding it?
daw
"Steve" wrote:
> I have a matrix that has columns for each quarter of the year and I added a
> subtotal colum for the year total. I would like to have the data cells in the
> subtotal column be a different color than that of the the quarter columns. I
> thought that using the little green triangle in the subtotal cell would work,
> but it doesn't. How can I do this?
> Thanks...
> Regards,
> Steve|||For some reason I can't get it to work on a column subtotal, but it works
fine for a row subtotal. No expressions overriding it far as I can see.
The BackgroundColor for the column heading is LightSkyBlue, but changing the
BackgroundColor on the subtotals textbox to, say Red, using the little green
arrow has no effect.
I was able to get it to work by setting the BackgroundColor property in the
data cell, i.e. the one with the sum in it, using this expression:
=iif(InScope("matrix3_ColumnGroup2"),"Quarter totals","Year totals")
Thanks,
Steve
"daw" wrote:
> It is the little green triangle - BackgroundColor in Subtotal properties.
> You also have to assign a BackgroundColor for the textbox that is your column
> heading.
> Do you have some expression overriding it?
> daw
> "Steve" wrote:
> > I have a matrix that has columns for each quarter of the year and I added a
> > subtotal colum for the year total. I would like to have the data cells in the
> > subtotal column be a different color than that of the the quarter columns. I
> > thought that using the little green triangle in the subtotal cell would work,
> > but it doesn't. How can I do this?
> >
> > Thanks...
> >
> > Regards,
> > Steve
Format Masking for creating leading periods
Is there an elegant way to fill the empty space leading a textbox in a table with periods, similar to a table of contents effect?
When I do not allow the textbox to grow and just append a long string of periods it looks fine in my report preview, but after I deploy everything appended after the primary field in that text box is missing?
My report has a lot of data to the right but it is collapsible so the fields to the left are a good distance away. This is why I am trying to include light visual aids that assist lining up data values. I am not stuck on the leading period idea but it seems the least cluttered.
AaronBump...
Does anyone have any solutions for masking fields?|||
Hello Aaron,
Can you try something like this in your field's expression:
=StrDup(25 - len(Fields!FIELD1.Value), ".") & Fields!FIELD1.Value
The 25 in my example would have to be higher than the maximum number of characters in your FIELD1 field. This will fill the rest of space (up to 25 characters) on the left of the field with period's, it should be adjusted to fill the rest of your textbox.
Hope this helps.
Jarret
Format issues - two-column report
I have a report the gets the following data for each member in a report
table format, this query takes parameters and returns about 20 records
and each has the same information shown below.
Name
company
title
address
phone
email
effective date
However, instead of this data "snaking" to the next column it prints
our five pages of this data with one column. I selected "2 columns"
for the body, and increased the body width, but only one column shows
up in the preview mode and afer I deploy the report. What can I do to
make this report have two columns and have a max of four rows per page,
with two records in each column?Try exporting to a paginated output format like Acrobat or TIFF and see if
it snakes then. It will only print one column in HTML.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"newbie_dba" <gnice4ever@.yahoo.com> wrote in message
news:1102956134.674586.303090@.z14g2000cwz.googlegroups.com...
>I have a report the gets the following data for each member in a report
> table format, this query takes parameters and returns about 20 records
> and each has the same information shown below.
>
> Name
> company
> title
> address
> phone
> email
> effective date
>
> However, instead of this data "snaking" to the next column it prints
> our five pages of this data with one column. I selected "2 columns"
> for the body, and increased the body width, but only one column shows
> up in the preview mode and afer I deploy the report. What can I do to
> make this report have two columns and have a max of four rows per page,
> with two records in each column?
>|||"Try exporting to a paginated output format like Acrobat or TIFF and
see if
it snakes then. It will only print one column in HTML."
Still doesn't work any other suggestions. Does there have to be a
group by clause in the report?|||Using the list layout object and exporting to Acrobat or TIFF does show
multi-column reports.
table format, this query takes parameters and returns about 20 records
and each has the same information shown below.
Name
company
title
address
phone
effective date
However, instead of this data "snaking" to the next column it prints
our five pages of this data with one column. I selected "2 columns"
for the body, and increased the body width, but only one column shows
up in the preview mode and afer I deploy the report. What can I do to
make this report have two columns and have a max of four rows per page,
with two records in each column?Try exporting to a paginated output format like Acrobat or TIFF and see if
it snakes then. It will only print one column in HTML.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"newbie_dba" <gnice4ever@.yahoo.com> wrote in message
news:1102956134.674586.303090@.z14g2000cwz.googlegroups.com...
>I have a report the gets the following data for each member in a report
> table format, this query takes parameters and returns about 20 records
> and each has the same information shown below.
>
> Name
> company
> title
> address
> phone
> effective date
>
> However, instead of this data "snaking" to the next column it prints
> our five pages of this data with one column. I selected "2 columns"
> for the body, and increased the body width, but only one column shows
> up in the preview mode and afer I deploy the report. What can I do to
> make this report have two columns and have a max of four rows per page,
> with two records in each column?
>|||"Try exporting to a paginated output format like Acrobat or TIFF and
see if
it snakes then. It will only print one column in HTML."
Still doesn't work any other suggestions. Does there have to be a
group by clause in the report?|||Using the list layout object and exporting to Acrobat or TIFF does show
multi-column reports.
FORMAT ISSUE... what to do?
Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon
format issue
I've created a new calculate member in (SQL Server Business Intelligence Studio 2005) with the following format string: "#,#0.00". This measure is displayed like this: 22,250.22
Nummer is formatted like this: 123 456 789,00
Then I created a Measure in the Cube structure menue and used the same format string:"#,#0.00" and this measure is displayed like this:0,66 -->
Comma and decimal point are changed in the display of the measures!!!!
Can anyone help me with this? I just like every measure to use a decimal point for the decimal place or the other way round. But I want every measure to be displayed in the same format!
Thanks a lot!
We've had a problem like that too. As far as I can remember, it is a known bug where calculated members are formatted according to the server locale instead of the client locale.
Verify which is the account that your SSAS service is running under, and then verify the locale of that account.
By the way, we're also having problems when the calculated member is a division, and the result is close to zero, the format switches to scientific notation (like 3.45E-7) instead of showing 0.00.
|||But I get different results from the same SSAS installation using the same format ?!|||If I understood your question correctly you get the expected format for regular measures and the wrong format for calculated measures. The bug only applies to calculated measures.|||Have you solved the problem or do you know a workaround?|||I don't know a workaround. We are just lucky that all users across the world accept a US locale, so we set the account of SSAS to a US locale. This way regular measures and calculated measures show in the same format. I assume this will be fixed in SP2.|||Here is a link to a BLOG that might solve your problem:http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
I have seen the same problem because i work in a scandinavian country. If I have a column that indicates the currency I simply do like this (### ### ### ###.##).
Note that you will have to use space as thousand separator.
With my language settings SSAS2005 thinks that a (,) is a thousand separator and a (.) is always a decimal indicator.
This is quite stupid but it is the way it works presently in SSAS2005. It will also cascade as a problem in Excel because if you do not get it right all measures will be converted to strings in Excel, if you use language settings different from US-settings.
HTH
Thomas Ivarsson
|||(### ### ### ###.##) thanks for this hint but unfortunately it doesn't work either. I used this format for both measures but in one , appeared as the comma seperator and in the other the decimal point appeared.Is there an official site where this bug is admited by Microsoft?
Thanks!
|||
OK. What is your regional/national settings for the O/S?
What thousand separator and what decimal sign are you using with these settings? look at regional settings in the control panel.
Does this problem appear in the BI-Dev Studion Browser or in a client, like Excel?
I have found it to work on clients with swedish settings( space as thousant separator and decimal(,) as decimal sign). In USA the dot is used as deciaml sign and decimal as thousand separator.
I recommend you to have a look at Moshas Blog entry of how to solve this in script.
Regards
Thomas Ivarsson
|||Regional Language settings are swedish.Nummer is formatted like this: 123 456 789,00
It appears in the BI-Dev studio and in the Management studio.
I created a measure in the Cube structure with the following format: ### ### ###.## -->result: 22 387.31
and I created a measure in the calculation tab with the following format: "### ### ###.##" --> result: ,66
I have no idea how to solve this. Is there an official Microsoft page where this bug is recognized?
Any more suggestions?|||
Hello again. So we are both swedish.
I have tried the same scenario you are describing but I do not get the same result for the calculated member.
Both "### ### ###.##" in a measure and in a calculated measure returns 000 000 000.00, with my swedish regional settings in the O/S. Check the sign for thousand separator and decimal in regional settings.
This is not correct because we use (,) as decimal sign but it will secure that client applications will fomat values in the correct way. Else, values can be converted to text in Excel.
I have heard som "talk" that the currency format in SSAS2005 will automatically turn in to the currency settings in your O/S. This have not happened yet om my machine.
My assumption is that, standard format settings, have to do with the NET-framework on your machine.
Have you tried the script, for formatting, in Moshas Blog that I have pointed at?
Regards
Thomas Ivarsson
|||
To reproduce the problem, you need to have the client in one locale, and the account under which the SSAS service is running in another locale. In case the service is running under a non domain user, you need to check the "Apply all settings to the current user account and to the default profile" box in Regional and Language Options, Advanced tab.
|||
Hello,
I had this problem because the collation of my AS2005 server was diferent from the collation of my cube.
Can you confirm if both settings are the same?
Best Regards
|||Hi,where can I look the collation up?
Thanks!|||
Right click on SSAS2005-server in management studio.
Select properties.
Select languages/collation.
HTH
Thomas
format issue
I've created a new calculate member in (SQL Server Business Intelligence Studio 2005) with the following format string: "#,#0.00". This measure is displayed like this: 22,250.22
Nummer is formatted like this: 123 456 789,00
Then I created a Measure in the Cube structure menue and used the same format string:"#,#0.00" and this measure is displayed like this:0,66 -->
Comma and decimal point are changed in the display of the measures!!!!
Can anyone help me with this? I just like every measure to use a decimal point for the decimal place or the other way round. But I want every measure to be displayed in the same format!
Thanks a lot!
We've had a problem like that too. As far as I can remember, it is a known bug where calculated members are formatted according to the server locale instead of the client locale.
Verify which is the account that your SSAS service is running under, and then verify the locale of that account.
By the way, we're also having problems when the calculated member is a division, and the result is close to zero, the format switches to scientific notation (like 3.45E-7) instead of showing 0.00.
|||But I get different results from the same SSAS installation using the same format ?!|||If I understood your question correctly you get the expected format for regular measures and the wrong format for calculated measures. The bug only applies to calculated measures.|||Have you solved the problem or do you know a workaround?|||I don't know a workaround. We are just lucky that all users across the world accept a US locale, so we set the account of SSAS to a US locale. This way regular measures and calculated measures show in the same format. I assume this will be fixed in SP2.|||Here is a link to a BLOG that might solve your problem:http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
I have seen the same problem because i work in a scandinavian country. If I have a column that indicates the currency I simply do like this (### ### ### ###.##).
Note that you will have to use space as thousand separator.
With my language settings SSAS2005 thinks that a (,) is a thousand separator and a (.) is always a decimal indicator.
This is quite stupid but it is the way it works presently in SSAS2005. It will also cascade as a problem in Excel because if you do not get it right all measures will be converted to strings in Excel, if you use language settings different from US-settings.
HTH
Thomas Ivarsson
|||(### ### ### ###.##) thanks for this hint but unfortunately it doesn't work either. I used this format for both measures but in one , appeared as the comma seperator and in the other the decimal point appeared.Is there an official site where this bug is admited by Microsoft?
Thanks!
|||
OK. What is your regional/national settings for the O/S?
What thousand separator and what decimal sign are you using with these settings? look at regional settings in the control panel.
Does this problem appear in the BI-Dev Studion Browser or in a client, like Excel?
I have found it to work on clients with swedish settings( space as thousant separator and decimal(,) as decimal sign). In USA the dot is used as deciaml sign and decimal as thousand separator.
I recommend you to have a look at Moshas Blog entry of how to solve this in script.
Regards
Thomas Ivarsson
|||Regional Language settings are swedish.Nummer is formatted like this: 123 456 789,00
It appears in the BI-Dev studio and in the Management studio.
I created a measure in the Cube structure with the following format: ### ### ###.## -->result: 22 387.31
and I created a measure in the calculation tab with the following format: "### ### ###.##" --> result: ,66
I have no idea how to solve this. Is there an official Microsoft page where this bug is recognized?
Any more suggestions?|||
Hello again. So we are both swedish.
I have tried the same scenario you are describing but I do not get the same result for the calculated member.
Both "### ### ###.##" in a measure and in a calculated measure returns 000 000 000.00, with my swedish regional settings in the O/S. Check the sign for thousand separator and decimal in regional settings.
This is not correct because we use (,) as decimal sign but it will secure that client applications will fomat values in the correct way. Else, values can be converted to text in Excel.
I have heard som "talk" that the currency format in SSAS2005 will automatically turn in to the currency settings in your O/S. This have not happened yet om my machine.
My assumption is that, standard format settings, have to do with the NET-framework on your machine.
Have you tried the script, for formatting, in Moshas Blog that I have pointed at?
Regards
Thomas Ivarsson
|||
To reproduce the problem, you need to have the client in one locale, and the account under which the SSAS service is running in another locale. In case the service is running under a non domain user, you need to check the "Apply all settings to the current user account and to the default profile" box in Regional and Language Options, Advanced tab.
|||
Hello,
I had this problem because the collation of my AS2005 server was diferent from the collation of my cube.
Can you confirm if both settings are the same?
Best Regards
|||Hi,where can I look the collation up?
Thanks!|||
Right click on SSAS2005-server in management studio.
Select properties.
Select languages/collation.
HTH
Thomas
Format in SP
Hi,
I am quering a table that has a bit field. The bit field is used to store a
boolean and I want to display this value in a data grid. Can I format(i do
not want a -1 or 0 displayed in the datagrid) this value before I return the
resultset?
ThanksWell, first off, a BIT cannot be -1. You must be thinking of Access or VB.
SELECT CASE bitColumn WHEN 1 THEN 'True' ELSE 'False' END FROM table
"jake" <jp@.broncos.com> wrote in message
news:uhRdKH20FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am quering a table that has a bit field. The bit field is used to store
> a boolean and I want to display this value in a data grid. Can I format(i
> do not want a -1 or 0 displayed in the datagrid) this value before I
> return the resultset?
> Thanks
>|||While you can use a CASE statement to transform the values, you are mingling
the data layer and the presentation layer. You can just as easily perform
the same action in your client code and maintain the tier integrity. (What
if you or someone else want to call this same query in another place and kee
p
the bit values?)
Just something to consider.
John Scragg
"jake" wrote:
> Hi,
> I am quering a table that has a bit field. The bit field is used to store
a
> boolean and I want to display this value in a data grid. Can I format(i d
o
> not want a -1 or 0 displayed in the datagrid) this value before I return t
he
> resultset?
> Thanks
>
>|||> (What if you or someone else want to call this same query in another place
and > keep the bit values?)
The 'wtf-way' would be to convert the values back and forth as needed. :)
I whole-heartedly agree - things like these belong on the presentation layer
.
ML
I am quering a table that has a bit field. The bit field is used to store a
boolean and I want to display this value in a data grid. Can I format(i do
not want a -1 or 0 displayed in the datagrid) this value before I return the
resultset?
ThanksWell, first off, a BIT cannot be -1. You must be thinking of Access or VB.
SELECT CASE bitColumn WHEN 1 THEN 'True' ELSE 'False' END FROM table
"jake" <jp@.broncos.com> wrote in message
news:uhRdKH20FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am quering a table that has a bit field. The bit field is used to store
> a boolean and I want to display this value in a data grid. Can I format(i
> do not want a -1 or 0 displayed in the datagrid) this value before I
> return the resultset?
> Thanks
>|||While you can use a CASE statement to transform the values, you are mingling
the data layer and the presentation layer. You can just as easily perform
the same action in your client code and maintain the tier integrity. (What
if you or someone else want to call this same query in another place and kee
p
the bit values?)
Just something to consider.
John Scragg
"jake" wrote:
> Hi,
> I am quering a table that has a bit field. The bit field is used to store
a
> boolean and I want to display this value in a data grid. Can I format(i d
o
> not want a -1 or 0 displayed in the datagrid) this value before I return t
he
> resultset?
> Thanks
>
>|||> (What if you or someone else want to call this same query in another place
and > keep the bit values?)
The 'wtf-way' would be to convert the values back and forth as needed. :)
I whole-heartedly agree - things like these belong on the presentation layer
.
ML
format in pdf
Hy,
I would like to convert my report in pdf. Do you have the code to convert it? I found a lot of code for crystal report but not for Reporting service.
thank you
oolon
In fact, I try to use this kind of code
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null, filename = "NorthwindCustomers.pdf";
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
ResultStream = rs.Render("/Northwind Customers", "PDF", null,
"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,
null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
// Creating a verbatim string.
FileStream stream = File.OpenWrite(@."C:\Articles\SQL Server Reporting
Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);
stream.Write(ResultStream, 0, ResultStream.Length);
stream.Close();
But my code doesn't recognize The class Reporting Service. Why? i put the librairies :
using System;
using System.IO;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
What librairy's missing?
Thank you
|||You need to create the proxy class, if you haven't already done this. Take a look at this page for instructions:http://msdn2.microsoft.com/en-us/library/ms155134.aspx
If you use the Web Reference method, the proxy classes are generated in the default namespace of your project with the reference name you specified in the Web Reference tool.
Format HTMLOWC
I have a .net application already that does quite a bit
of reporting and I'd like to incorporate RS into it.
Currently, I have a DetailReport.aspx page that displays
results using Office Web Components. I'd like to replace
this page with reports from RS. When I try to view
reports using the Format=HTMLOWC directly in the HTML
Viewer, the results look just like regular HTML (not like
the Excel in OWC). I thought I could just display the
report using the ReportViewer.dll included with RS, but
it doesn't seem like that will work.
So, my question is what is the best way to incorporate RS
into my existing application? I want the user to be able
to experience the report in OWC through my current web
app.
Thanks in advance!
Jim
.What is is issue you have with ReportViewer control, because we are using
ReportViewer to display the report in the web interface and it works fine.
Let me know the issue and see how I can help you
Balaji
--
Message posted via http://www.sqlmonster.com|||Thanks for your response, but I'm not sure I understand. I changed the
webapp to use the reportviewer.dll control for RS and used the
Format="HTMLOWC" property, but the report is still rendered on the webform as
HTML and not HTML with OWC. Any ideas? Both the client and the IIS server
have OWC installd, but the RS Server may not.
JE
"BALAJI via SQLMonster.com" wrote:
> What is is issue you have with ReportViewer control, because we are using
> ReportViewer to display the report in the web interface and it works fine.
> Let me know the issue and see how I can help you
> Balaji
> --
> Message posted via http://www.sqlmonster.com
>|||Actually, I'm finding that I cannot even download the report using the Export
functionality in the HTML Viewer. It just shows up as regular HTML. I have
OWC installed on the client and it used to download the same report fine.
I've installed RS SP2 - could that have broken this?
JE
"Jim Emlet" wrote:
> Thanks for your response, but I'm not sure I understand. I changed the
> webapp to use the reportviewer.dll control for RS and used the
> Format="HTMLOWC" property, but the report is still rendered on the webform as
> HTML and not HTML with OWC. Any ideas? Both the client and the IIS server
> have OWC installd, but the RS Server may not.
> JE
> "BALAJI via SQLMonster.com" wrote:
> > What is is issue you have with ReportViewer control, because we are using
> > ReportViewer to display the report in the web interface and it works fine.
> >
> > Let me know the issue and see how I can help you
> >
> > Balaji
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||The OWC rendering has lots of limitations that are described in the
documentation. If we run into one of these restrictions, it will come out as
straight HTML.
The best way to embed reports into your applications is actually the new
ReportViewer controls in VS 2005.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Emlet" <JimEmlet@.discussions.microsoft.com> wrote in message
news:132259F7-7CAF-404C-9F04-8D8910BCC60A@.microsoft.com...
> Actually, I'm finding that I cannot even download the report using the
> Export
> functionality in the HTML Viewer. It just shows up as regular HTML. I
> have
> OWC installed on the client and it used to download the same report fine.
> I've installed RS SP2 - could that have broken this?
> JE
> "Jim Emlet" wrote:
>> Thanks for your response, but I'm not sure I understand. I changed the
>> webapp to use the reportviewer.dll control for RS and used the
>> Format="HTMLOWC" property, but the report is still rendered on the
>> webform as
>> HTML and not HTML with OWC. Any ideas? Both the client and the IIS
>> server
>> have OWC installd, but the RS Server may not.
>> JE
>> "BALAJI via SQLMonster.com" wrote:
>> > What is is issue you have with ReportViewer control, because we are
>> > using
>> > ReportViewer to display the report in the web interface and it works
>> > fine.
>> >
>> > Let me know the issue and see how I can help you
>> >
>> > Balaji
>> >
>> > --
>> > Message posted via http://www.sqlmonster.com
>> >
of reporting and I'd like to incorporate RS into it.
Currently, I have a DetailReport.aspx page that displays
results using Office Web Components. I'd like to replace
this page with reports from RS. When I try to view
reports using the Format=HTMLOWC directly in the HTML
Viewer, the results look just like regular HTML (not like
the Excel in OWC). I thought I could just display the
report using the ReportViewer.dll included with RS, but
it doesn't seem like that will work.
So, my question is what is the best way to incorporate RS
into my existing application? I want the user to be able
to experience the report in OWC through my current web
app.
Thanks in advance!
Jim
.What is is issue you have with ReportViewer control, because we are using
ReportViewer to display the report in the web interface and it works fine.
Let me know the issue and see how I can help you
Balaji
--
Message posted via http://www.sqlmonster.com|||Thanks for your response, but I'm not sure I understand. I changed the
webapp to use the reportviewer.dll control for RS and used the
Format="HTMLOWC" property, but the report is still rendered on the webform as
HTML and not HTML with OWC. Any ideas? Both the client and the IIS server
have OWC installd, but the RS Server may not.
JE
"BALAJI via SQLMonster.com" wrote:
> What is is issue you have with ReportViewer control, because we are using
> ReportViewer to display the report in the web interface and it works fine.
> Let me know the issue and see how I can help you
> Balaji
> --
> Message posted via http://www.sqlmonster.com
>|||Actually, I'm finding that I cannot even download the report using the Export
functionality in the HTML Viewer. It just shows up as regular HTML. I have
OWC installed on the client and it used to download the same report fine.
I've installed RS SP2 - could that have broken this?
JE
"Jim Emlet" wrote:
> Thanks for your response, but I'm not sure I understand. I changed the
> webapp to use the reportviewer.dll control for RS and used the
> Format="HTMLOWC" property, but the report is still rendered on the webform as
> HTML and not HTML with OWC. Any ideas? Both the client and the IIS server
> have OWC installd, but the RS Server may not.
> JE
> "BALAJI via SQLMonster.com" wrote:
> > What is is issue you have with ReportViewer control, because we are using
> > ReportViewer to display the report in the web interface and it works fine.
> >
> > Let me know the issue and see how I can help you
> >
> > Balaji
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||The OWC rendering has lots of limitations that are described in the
documentation. If we run into one of these restrictions, it will come out as
straight HTML.
The best way to embed reports into your applications is actually the new
ReportViewer controls in VS 2005.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Emlet" <JimEmlet@.discussions.microsoft.com> wrote in message
news:132259F7-7CAF-404C-9F04-8D8910BCC60A@.microsoft.com...
> Actually, I'm finding that I cannot even download the report using the
> Export
> functionality in the HTML Viewer. It just shows up as regular HTML. I
> have
> OWC installed on the client and it used to download the same report fine.
> I've installed RS SP2 - could that have broken this?
> JE
> "Jim Emlet" wrote:
>> Thanks for your response, but I'm not sure I understand. I changed the
>> webapp to use the reportviewer.dll control for RS and used the
>> Format="HTMLOWC" property, but the report is still rendered on the
>> webform as
>> HTML and not HTML with OWC. Any ideas? Both the client and the IIS
>> server
>> have OWC installd, but the RS Server may not.
>> JE
>> "BALAJI via SQLMonster.com" wrote:
>> > What is is issue you have with ReportViewer control, because we are
>> > using
>> > ReportViewer to display the report in the web interface and it works
>> > fine.
>> >
>> > Let me know the issue and see how I can help you
>> >
>> > Balaji
>> >
>> > --
>> > Message posted via http://www.sqlmonster.com
>> >
Format Help
i'm trying to format integers to have ,s... like 1,000 instead of 1000.
??????You want commas in your database? Don't you only want the commas in your application (front-end)?|||Output formatting really ought to be done on the client side, for a number of reasons.
First and foremost, if you format the data at the server you create a logistical nightmare if/when you need to expand to support multiple locales. You'll have to write code everywhere you do formatting to determine how the data needs to be formatted for each case. This is an exercise in quick ways to create code that will drive your insane at some later date.
From a pure performance basis, it is a poor choice to format your data before you need to. The formatting will inhibit you from being able to do any additional processing. It is really hard to add a 7.5% VAT to "$1,000.00" in the United Kingdom, while it is trivial to add the VAT to the unformatted monetary value which will also format correctly when it reaches the client!
-PatP|||what i'm trying to do is write a select statement that would add the ,s in...
is that possible??|||Nearly anything is possible. I'm pretty sure that it isn't a good idea. I would very strongly advise that you do your formatting on the client side, using a tool such as Crystal Reports, MS-Access, VB, etc instead of formatting the data on the server.
-PatP
??????You want commas in your database? Don't you only want the commas in your application (front-end)?|||Output formatting really ought to be done on the client side, for a number of reasons.
First and foremost, if you format the data at the server you create a logistical nightmare if/when you need to expand to support multiple locales. You'll have to write code everywhere you do formatting to determine how the data needs to be formatted for each case. This is an exercise in quick ways to create code that will drive your insane at some later date.
From a pure performance basis, it is a poor choice to format your data before you need to. The formatting will inhibit you from being able to do any additional processing. It is really hard to add a 7.5% VAT to "$1,000.00" in the United Kingdom, while it is trivial to add the VAT to the unformatted monetary value which will also format correctly when it reaches the client!
-PatP|||what i'm trying to do is write a select statement that would add the ,s in...
is that possible??|||Nearly anything is possible. I'm pretty sure that it isn't a good idea. I would very strongly advise that you do your formatting on the client side, using a tool such as Crystal Reports, MS-Access, VB, etc instead of formatting the data on the server.
-PatP
Format function in SQL
Hi ... i'm sorry to hassle this user group but i have an urgent need
for some code - i have tried and tried to find a solutionn elsewhere -
but the problem is i don't really know what i am looking for.
for some code - i have tried and tried to find a solutionn elsewhere -
but the problem is i don't really know what i am looking for.
What is the equivalent SLQ code for the below statement which works in
MS Access?
Format(7,"00")
Result: 07
or alternatively ...
what i am actually trying to do is return the string yyyymm based on
the current date. eg 200506 (June 2006)
Is there a simple way of doing this?
The code i am currently using is
CONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,
GETDATE()))
Result: 2005 !!!!
At least i thought i would get 20056. But obv what i am aiming for is
200506.
Thanks in advance.
TCSee the various formats under CONVERT in Books Online:
select convert(char(6), current_timestamp, 112)
But in general, it's better to format output in the front end, not in
the database.
Simon|||To format a number as text with a leading zero, you can use the
following trick:
SELECT RIGHT('0'+CONVERT(varchar(2),YourNumber),2)
To get the year and month of a date, formatted as "yyyymm" you can use:
SELECT CONVERT(varchar(4),YEAR(GETDATE()))
+RIGHT('0'+CONVERT(varchar(2),MONTH(GETDATE())),2)
or:
SELECT CONVERT(varchar(6),GETDATE(),112)
By the way, your code doesn't return '2005', as you think; it returns:
'2005 6 '
This is because the char data type has a fixed length (it doesn't trim
trailing blanks) and because the default size of a char is 30.
For more informations about datetime data types, see:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan|||thanks heaps - the SELECT CONVERT(varchar(6),GETDATE(),1*12) worked a
treat!
exactly what i needed
cheers,
TC|||(tcumming@.smorgonsteel.com.au) writes:
> Hi ... i'm sorry to hassle this user group but i have an urgent need
> for some code - i have tried and tried to find a solutionn elsewhere -
> but the problem is i don't really know what i am looking for.
> What is the equivalent SLQ code for the below statement which works in
> MS Access?
> Format(7,"00")
While you already have gotten help with your urgent needs, permit me
to point out that Functions->String Functions in the T-SQL Reference
of Books Online is a good place to start. That and the CAST and
CONVERT topic.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Format for Parameters
Simple question - I have a date as a parameter. When I enter 12/1/2004 for
example and run the report, after the data is displayed, the parameter
displays "12/1/2004 12:00:00AM". Is there any way to pass the typical date
format like "MM/dd/yyyy" to the parameter so that it formats the date only?
thanks
billHi Bill,
Based on my knowledge, we are unable to format the
parameters. But I would like to perform further research
to confirm if this is possible. I will update you once I
have more information.
BTW: If you display the date in a text box in the Report
body, you can set the format via the text box Properties
dialog box.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: <sutphinwb@.nospam.nospam>
>From: <sutphinwb@.nospam.nospam>
>Subject: Format for Parameters
>Date: Wed, 2 Feb 2005 00:37:39 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: ip24-250-32-203.ri.ri.cox.net
24.250.32.203
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:41614
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Simple question - I have a date as a parameter. When I
enter 12/1/2004 for
>example and run the report, after the data is
displayed, the parameter
>displays "12/1/2004 12:00:00AM". Is there any way to
pass the typical date
>format like "MM/dd/yyyy" to the parameter so that it
formats the date only?
>thanks
>bill
>
>|||William is correct to the best of my knowledge as well... If you need pretty
formatting either use a string, check that it is a valid date, and format it
as you wish... You could also use an HTML page as a front end to the report,
formating date strings etc using html techniques, then passing the params to
the URL... ( I prefer the simpler string method if possible.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<sutphinwb@.nospam.nospam> wrote in message
news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> Simple question - I have a date as a parameter. When I enter 12/1/2004
for
> example and run the report, after the data is displayed, the parameter
> displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
date
> format like "MM/dd/yyyy" to the parameter so that it formats the date
only?
> thanks
> bill
>|||You can use a stored procedure to retrieve the data from the database. And
then you can format the parameter inside the stored procedure.
"Wayne Snyder" wrote:
> William is correct to the best of my knowledge as well... If you need pretty
> formatting either use a string, check that it is a valid date, and format it
> as you wish... You could also use an HTML page as a front end to the report,
> formating date strings etc using html techniques, then passing the params to
> the URL... ( I prefer the simpler string method if possible.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <sutphinwb@.nospam.nospam> wrote in message
> news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> > Simple question - I have a date as a parameter. When I enter 12/1/2004
> for
> > example and run the report, after the data is displayed, the parameter
> > displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
> date
> > format like "MM/dd/yyyy" to the parameter so that it formats the date
> only?
> >
> > thanks
> >
> > bill
> >
> >
>
>
example and run the report, after the data is displayed, the parameter
displays "12/1/2004 12:00:00AM". Is there any way to pass the typical date
format like "MM/dd/yyyy" to the parameter so that it formats the date only?
thanks
billHi Bill,
Based on my knowledge, we are unable to format the
parameters. But I would like to perform further research
to confirm if this is possible. I will update you once I
have more information.
BTW: If you display the date in a text box in the Report
body, you can set the format via the text box Properties
dialog box.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: <sutphinwb@.nospam.nospam>
>From: <sutphinwb@.nospam.nospam>
>Subject: Format for Parameters
>Date: Wed, 2 Feb 2005 00:37:39 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: ip24-250-32-203.ri.ri.cox.net
24.250.32.203
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:41614
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Simple question - I have a date as a parameter. When I
enter 12/1/2004 for
>example and run the report, after the data is
displayed, the parameter
>displays "12/1/2004 12:00:00AM". Is there any way to
pass the typical date
>format like "MM/dd/yyyy" to the parameter so that it
formats the date only?
>thanks
>bill
>
>|||William is correct to the best of my knowledge as well... If you need pretty
formatting either use a string, check that it is a valid date, and format it
as you wish... You could also use an HTML page as a front end to the report,
formating date strings etc using html techniques, then passing the params to
the URL... ( I prefer the simpler string method if possible.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<sutphinwb@.nospam.nospam> wrote in message
news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> Simple question - I have a date as a parameter. When I enter 12/1/2004
for
> example and run the report, after the data is displayed, the parameter
> displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
date
> format like "MM/dd/yyyy" to the parameter so that it formats the date
only?
> thanks
> bill
>|||You can use a stored procedure to retrieve the data from the database. And
then you can format the parameter inside the stored procedure.
"Wayne Snyder" wrote:
> William is correct to the best of my knowledge as well... If you need pretty
> formatting either use a string, check that it is a valid date, and format it
> as you wish... You could also use an HTML page as a front end to the report,
> formating date strings etc using html techniques, then passing the params to
> the URL... ( I prefer the simpler string method if possible.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <sutphinwb@.nospam.nospam> wrote in message
> news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> > Simple question - I have a date as a parameter. When I enter 12/1/2004
> for
> > example and run the report, after the data is displayed, the parameter
> > displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
> date
> > format like "MM/dd/yyyy" to the parameter so that it formats the date
> only?
> >
> > thanks
> >
> > bill
> >
> >
>
>
format for Convert
Hello,
SELECT name + '- ' + CONVERT(varchar,amt) as ddlCap from myTable
How can I get amt be like "#0.00" format in ddlCap?
Hi,
Please run the below statement to see if it is what you want...
SELECT
amt,
CONVERT(varchar(20),amt,0),
CONVERT(varchar(20),amt,1)
FROM myTable
Eralper
http://www.kodyaz.com
Format for a bigint parameter in Raiserror
Having a small problem with RAISERROR that I've isolated to a small code sample:
The objective is to have the message string in Raiserror correctly report the value of a parameter that is a bigint. The following extract shows the problem:
ALTER PROCEDURE [dbo].[pr_Test]
@.SomeNumber bigint
AS
BEGIN
SET NOCOUNT ON;
RAISERROR('Test Error Number: %d.',16,1,@.SomeNumber)
END
As written, when the procedure is executed with the value 1 passed in the @.SomeNumber parameter, the result is:
Msg 2786, Level 16, State 1, Procedure pr_Test, Line 19
The data type of substitution parameter 1 does not match the expected type of the format specification.
If the datatype for @.SomeNumber is changed to int, then the procedure executes correctly and reports 'Test Error Number: 1'. Any suggestions on how to fix this problem?
Regards,
Flavelle
There seems to a limitation with the numeric data type while using the Raiserror statement.
My suggestion is to cast the bigint to a varchar Here is the modified code block
alter PROCEDURE [dbo].[pr_Test]
@.SomeNumber BIGINT
AS
BEGIN
DECLARE @.NewNumber VARCHAR(1000)
SET @.NewNumber=CAST(@.SomeNumber AS VARCHAR(100))
SET NOCOUNT ON;
RAISERROR('Test Error Number: %s',16,1, @.NewNumber)
END
|||Have you looked at the definition for RAISERROR in Books Online? It says bigint is not a valid datatype for that parameter.
argument
Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.
|||yes, i misread your post, and yes, casting/converting to a varchar datatype is a simple fix/workaround.|||
My thanks to you both - brokenrulz for the solution to the problem and Greg for reminding me that I have to remember to read the ENTIRE help file. Your reference to the supported datatypes for the parameters is deeply buried within the help topic.
Regards,
Flavelle
|||Hello,
Help files have been updated on July 2006 (http://msdn2.microsoft.com/en-us/library/ms178592.aspx) and it says "To convert a value to the Transact-SQL bigint data type, specify %I64d" (the letter before 64 begin a capital i).
Not need to cast the bigint into an nvarchar. I tested under SQL 2005 + SP (version 9.00.3054.00) and it works great.
Laurent
|||I was facing the same problem and your suggestion saved the day for me. A big thanks.Format for a bigint parameter in Raiserror
Having a small problem with RAISERROR that I've isolated to a small code sample:
The objective is to have the message string in Raiserror correctly report the value of a parameter that is a bigint. The following extract shows the problem:
ALTER PROCEDURE [dbo].[pr_Test]
@.SomeNumber bigint
AS
BEGIN
SET NOCOUNT ON;
RAISERROR('Test Error Number: %d.',16,1,@.SomeNumber)
END
As written, when the procedure is executed with the value 1 passed in the @.SomeNumber parameter, the result is:
Msg 2786, Level 16, State 1, Procedure pr_Test, Line 19
The data type of substitution parameter 1 does not match the expected type of the format specification.
If the datatype for @.SomeNumber is changed to int, then the procedure executes correctly and reports 'Test Error Number: 1'. Any suggestions on how to fix this problem?
Regards,
Flavelle
There seems to a limitation with the numeric data type while using the Raiserror statement.
My suggestion is to cast the bigint to a varchar Here is the modified code block
alter PROCEDURE [dbo].[pr_Test]
@.SomeNumber BIGINT
AS
BEGIN
DECLARE @.NewNumber VARCHAR(1000)
SET @.NewNumber=CAST(@.SomeNumber AS VARCHAR(100))
SET NOCOUNT ON;
RAISERROR('Test Error Number: %s',16,1, @.NewNumber)
END
|||Have you looked at the definition for RAISERROR in Books Online? It says bigint is not a valid datatype for that parameter.
argument
Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.
|||yes, i misread your post, and yes, casting/converting to a varchar datatype is a simple fix/workaround.|||
My thanks to you both - brokenrulz for the solution to the problem and Greg for reminding me that I have to remember to read the ENTIRE help file. Your reference to the supported datatypes for the parameters is deeply buried within the help topic.
Regards,
Flavelle
|||Hello,
Help files have been updated on July 2006 (http://msdn2.microsoft.com/en-us/library/ms178592.aspx) and it says "To convert a value to the Transact-SQL bigint data type, specify %I64d" (the letter before 64 begin a capital i).
Not need to cast the bigint into an nvarchar. I tested under SQL 2005 + SP (version 9.00.3054.00) and it works great.
Laurent
|||I was facing the same problem and your suggestion saved the day for me. A big thanks.Format Files - Bcp
Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> > Hi,
> >
> > I want to write a BCP format file, which uses one data column Value to map
> > to
> > Two Column Fields in the table.
> >
> > For Example:
> > Data File: Pin Value - "5600055"
> > Table Columns
> > 1) tmp_pin
> > 2) per_pin
> >
> > Is the above possible to specify in the format file? Does this flexibility
> > is provided by BCP Utility to load the data.
> >
> > Regards
> > Govardhan MV
> >
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> > Hi,
> >
> > I want to write a BCP format file, which uses one data column Value to map
> > to
> > Two Column Fields in the table.
> >
> > For Example:
> > Data File: Pin Value - "5600055"
> > Table Columns
> > 1) tmp_pin
> > 2) per_pin
> >
> > Is the above possible to specify in the format file? Does this flexibility
> > is provided by BCP Utility to load the data.
> >
> > Regards
> > Govardhan MV
> >
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV
Format Files - Bcp
Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>
|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>
|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
Format Files - Bcp
Hi,
I want to write a BCP format file, which uses one data column Value to map t
o
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>
I want to write a BCP format file, which uses one data column Value to map t
o
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)