How to enter this command line sql statement...??

:confused: I am trying to use a program called ODBCVIEW to query a database from the command line, and write it to text file. The instructions look easy, But I am illiterate to translating command line syntax.
I am not sure if I use the ">" , "[", etc... or if I leave them out. Can some one show me exactly how the finished code is supposed to look (in laymans)...

The program I am using to query the DB is called ODBCVIEW. Here is a link to the page with the syntax(located at the bottom) - http://www.slik.co.nz/HTML_help/odbc_view.htm

...and I have also pasted it below:

-COPIED FROM WEBSITE:
ODBCView also supports a non-interactive command line mode to execute a query and save the results to a text file. The syntax is as follows:

ODBCView.exe <DSN=DataSourceName;UID=User;PWD=Password> [<SQLScript.sql>] [<OutputFile.htm|csv|txt>]

Where:

DataSource
The desired datasource name.

UID
An optional user name to logon to the database

PWD
An optional user password

SQLScript
The query to execute.

OutputFile
The path to the output file. The files extension determines the format. Use htm or html to HTML, csv for CSV.If you are only trying to query a database and write the results to a file, better try WinSQL (http://www.download.com/3001-10255_4-10213451.html). ;)|||:D Just what I was looking for!

I'm installing it now - Does it have a 'command-line' mode?

How to enter a line feed in a formula field

I want to concatenate 3-4 fields in a formula field. The problem is i want each and every field in the next line. How can you insert a line feed in a formula field?
ThanksDon't know if this will help, but I'll try...

I'm using Crystal Reports 8.5. If I have a text field and a Database Field, all I have to is drag and drop the Database field onto the Text Field and they will be merged into one. You have to get it at the exact location in order for it to place one field inside another, otherwise it will just be placed on top of the other field. Keep playing around with it and you'll get it eventually.|||Originally posted by malleyo
Don't know if this will help, but I'll try...

I'm using Crystal Reports 8.5. If I have a text field and a Database Field, all I have to is drag and drop the Database field onto the Text Field and they will be merged into one. You have to get it at the exact location in order for it to place one field inside another, otherwise it will just be placed on top of the other field. Keep playing around with it and you'll get it eventually.

I am using Crystal Reports 4.5, Every field is a database field. I want to concatenate every field into a formula field such that every field starts at a new line.
If i place each field one below the other what happens is if the previous column in the report is multiline the placing of the fields in the last column where i have placed the fields one below the other becomes disturbed, with the first line taking the same number of lines as the previous column and then the other details are placed. I want the last column to be printed in the way I have placed the fields without bothering about the number of lines being taken by the previous column.

Thanks|||Please some one help me out with this problem. My project is being held up for implementation just for this problem

Help:( :(|||U can insert a line feed/carriage return by inserting chr(13) in your selection formula

ex:

StringVar x:= "Title for the Cross-Tab" + Chr(13) + "Period from Year X to Year Y"

Hope this will work|||my problem is in the second column i have an address field and it is a multiline field. in the third column i have set the tel fax email fields one below the other so that for one record the details of the address and the tel details etc fall on the same line. But the problem is since the address field is a multi line field the telephone field takes the the same size as the address field and the remaining fields come below that. I want the tel, email fax details one below the other irrespective of the size the address field takes.
Please help me .|||Can the address field be broken down into it's separate parts? For example, when I store an address, I store the Street Name and Number in one column, the City in another column, the State in a third column, and the ZipCode in a fourth column. Then I make a multiline text object and drop the fields into the text object in the order that I need them. See the attached image. I also don't know if 4.5 supports the drag and drop like 8.5 does.

Hope that helps!|||Thanks I got your point. But the address field cannot be broken in to smaller fields. Is there any other way. the formula editor doesn't supports chr function. Even if i pass it through a string function it comes in the same line and then after the field size is met moves to the next line. Is there any way we can call th CHR function in the formula editor

Thanks|||Chr(13) works fine when inserting within a formula.

ex
left({?inp string},3) & chr(13) & mid({?inp string},4,3)

Try to figure out how many chars are taken for telephone, fax, email (use some string functions to know where they are in a lengthier string) and then use chr(13) in between them.|||I am sorry to say but i tried using chr(13) in the formula editor but it doesn't works. when i check the formula it says string,numeric,currency or boolean required at the point where i have entered chr(13). Folllowing is the code entered in the formula editor.

"Tel No : " + {Supplier.TelNo} + chr(13) + " Fax No :" + {Supplier.Fax}

If chr(13) is accepted in the formula editor then there will be no problem. I can directly concatenate the database fields and where ever the field should be in the next line i can enter a line feed.

Help:confused: :confused:|||Strange!!!!
The same thing works for me. Are Tel ph, fax no fields are string / numeric.

Sorry, these may be idiotic ideas,

If they are numeric (hope it's not), try to convert them as string and try to use & instead + (not must, both will work)

Otherwise try to select the chr function from the function tree instead of typing it.|||Are u using CR 10?

If it is,

chr, asc functions are changed and the functions are now chrW, ascW.

Try them.|||I will try them out . But these functions are not there in the function list. thanks i will try and let you know

Thanks|||Originally posted by harmonycitra
Are u using CR 10?

If it is,

chr, asc functions are changed and the functions are now chrW, ascW.

Try them.

I am using Chr(10). I have tried out the above suggestion also but the same error persists. I am working with crystal reports 4.6.
The above functions doesn't exists in the function box in the formula editor.

I have tried to post the data from the application but the details are shown one after the other without linefeed.

Help:( :(|||I am using CR 4.5. There are not any CHR or ASC functions in CR4.5. How can i solve this problem|||I think you need both carriage return and linefeed which is chr(10) + char(13). Here is an example

CRXReport.FormulaFields.Item(1).text = chr(10)+chr(13) & Text1.text & chr(10)+chr(13)

There are two ways to deal with it.

1. Have your sql take care of this concatenation with carriage return and line feed. Have sql return address as one field and remaining all as second with carriage return and line feed.

2. Once done just display address field first and then second field. Check in 4.5 is there a property called can grow. Try disabling it if you could. As these two fields are going to be indendent CR wouldn't have any problem displaying it.

Thanks|||Originally posted by dilemma
I think you need both carriage return and linefeed which is chr(10) + char(13). Here is an example

CRXReport.FormulaFields.Item(1).text = chr(10)+chr(13) & Text1.text & chr(10)+chr(13)

There are two ways to deal with it.

1. Have your sql take care of this concatenation with carriage return and line feed. Have sql return address as one field and remaining all as second with carriage return and line feed.

2. Once done just display address field first and then second field. Check in 4.5 is there a property called can grow. Try disabling it if you could. As these two fields are going to be indendent CR wouldn't have any problem displaying it.

Thanks

I have tried passing the concatenated fields to the formula field in CR 4.5 but to no avail. The CR doesn't recognizes carriage return and line feed. There are also no chr functions the function list in the formula editor. After passing the concatenated to the CR the details come as a single line field.

CRXReport.FormulaFields.Item(1).text = chr(10)+chr(13) & Text1.text & chr(10)+chr(13).

The above syntax also doesn't works as there is no FormulaFields property in the CR control i use in my application.

Help

:confused: :confused:|||What if your backend deals with this scenario ? What backend you are using ?

Thanks|||I am using MSAccess as my backend. How can we handle this through the backend? I cannot make major changes to the database as i am in my last leg of the project. Because of the change to the database it should not be that i would have to change a lot in my code.

Thanks|||Is this a table based report or SQL based ? If it's a SQL based report then in sql itself concatenate char(10)+char(13).

eg. select name, code + char(10)+char(13) from table where ..

Thanks|||Originally posted by dilemma
Is this a table based report or SQL based ? If it's a SQL based report then in sql itself concatenate char(10)+char(13).

eg. select name, code + char(10)+char(13) from table where ..

Thanks

It is a table based report and not a sql based report. There are three different fields to be placed one below the other in the last column where as in the column previous to this column there is only one field which is the address field which is mutiline.|||I think the problem faced by me is solved. Instead of taking the fields directly from the table i have created a query where in i have joined all the fields in the last column with line feed and carriage return. when i place this field in the report i get the desired result.

Thanks for helping me out and for all the valuable

suggestion:wave: :)|||Hello
As far as i understand your problem. Here is the solution: (Try it and do tell me) I have not VS.Net at this time thats why check the syntax urself

' First take global variable and place it in the report header
' and suppress it (so that it would not display at run time)
global var as integer
var = 0
dim store as integer
'''''
' Then in your formula Field check this variable equal to zero
if var==0 then
store = field1 + field2 + field3
var=1
else if var==1 then
store = store + field1 + field2 + field3
end if

Best of Luck|||Hi, Im not sure it will work but i try...

I was thinking and maybe you can separate the address by formulas in order to try the other sugestion.

You can know the size of the address string by "Length" function. It appears in the Strings' functions. Then if you know how many numbers have the fax and the telephone you substract the quantity and with te "Mid" function you can extract the information.

Obviously you have to do a formula for each elemento of the address.

I hope it helps you|||I am sorry to say but i tried using chr(13) in the formula editor but it doesn't works. when i check the formula it says string,numeric,currency or boolean required at the point where i have entered chr(13). Folllowing is the code entered in the formula editor.

"Tel No : " + {Supplier.TelNo} + chr(13) + " Fax No :" + {Supplier.Fax}

If chr(13) is accepted in the formula editor then there will be no problem. I can directly concatenate the database fields and where ever the field should be in the next line i can enter a line feed.

Help:confused: :confused:

Make sure you have Crystal Syntax turned on if you are using "+" to concatenate strings, otherwise you will need to use "&".

How to drop sql server object?

Hi,
I am getting errors when I try to drop a column in sql server table.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__hdp_langu__langn__29572725' is dependent on column
'langname_ge'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
access this column.
I thought the object to be a constraint and tried dropping it first by
"alter table drop constraint" command.
But query analyser says that the concerned object is not a constraint. How
do I drop this object?
Thanks and Regards,
Celiacelia (celia.rexselin@.gmail.com) writes:
> I am getting errors when I try to drop a column in sql server table.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__hdp_langu__langn__29572725' is dependent on column
> 'langname_ge'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
> access this column.
> I thought the object to be a constraint and tried dropping it first by
> "alter table drop constraint" command.
> But query analyser says that the concerned object is not a constraint. How
> do I drop this object?
In this particular case you would do:
ALTER TABLE tbl DROP CONSTRAINT DF__hdp_langu__langn__29572725
A tip is that when you create tables is to always name your constraints
explicitly, that makes it easier to drop them. For instance:
CREATE TABLE a (a int NOT NULL,
b int NOT NULL CONSTRAINT df DEFAULT 12)
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|||You could search for dependents of the object you are trying to drop. For
example, if the object you are trying to drop is called 'ObjectName', then
try:
Select
ID ,
Object_Name( ID ) As Object ,
DepID ,
Object_Name( DepID ) As Dependent
From
SysDepends
Where
Id = Object_ID( 'ObjectName' )
"celia" <celia.rexselin@.gmail.com> wrote in message
news:Ooa$L1FFGHA.644@.TK2MSFTNGP09.phx.gbl...
Hi,
I am getting errors when I try to drop a column in sql server table.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__hdp_langu__langn__29572725' is dependent on column
'langname_ge'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
access this column.
I thought the object to be a constraint and tried dropping it first by
"alter table drop constraint" command.
But query analyser says that the concerned object is not a constraint. How
do I drop this object?
Thanks and Regards,
Celia|||Like everyone else has said, you have to drop the defaults and check
constraints first.
I have made a suggestion a while back on the feedback center. Please vote
for it :)
http://lab.msdn.microsoft.com/produ...14-4f026070abac
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"celia" <celia.rexselin@.gmail.com> wrote in message
news:Ooa$L1FFGHA.644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am getting errors when I try to drop a column in sql server table.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__hdp_langu__langn__29572725' is dependent on column
> 'langname_ge'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
> access this column.
> I thought the object to be a constraint and tried dropping it first by
> "alter table drop constraint" command.
> But query analyser says that the concerned object is not a constraint. How
> do I drop this object?
> Thanks and Regards,
> Celia
>
>

how to draw a straight line in the chart?

I want to draw a straight line whose parameter can be set in a
variaty.how to get it?If this is a line chart or a column chart, you could just add another data
series and plot that series as line (in case of a column chart, select the
"plot data as line" checkbox on the appearance tab). Set the data value
expression a parameter-dependent value, e.g. =Parameters!Threshold.Value and
set the BorderColor property
accordingly.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spirit" <qmxlf@.163.com> wrote in message
news:1130427418.066195.117940@.g47g2000cwa.googlegroups.com...
>I want to draw a straight line whose parameter can be set in a
> variaty.how to get it?
>|||select '2' as constant from .........
do you mean like that?|||You could also do it by using expressions. Attached a sample report at the
bottom of this posting which has a dynamic target line in the chart.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spirit" <qmxlf@.163.com> wrote in message
news:1130601362.110856.78230@.g49g2000cwa.googlegroups.com...
> select '2' as constant from .........
> do you mean like that?
============================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>Blue</BackgroundColor>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>Yellow</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>=iif((Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15 > 100000, "Aqua", "Green")</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
<Style>
<BackgroundColor>Yellow</BackgroundColor>
</Style>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=iif(Year(Fields!OrderDate.Value) = 1996, 75000,
iif(Year(Fields!OrderDate.Value) = 1997, 90000, 115000))</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>10pt</Default>
</BorderWidth>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<Format>MM/yyyy</Format>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Column</Type>
<Title>
<Caption>Sales / Cost / Target</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)*100+Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!OrderDate.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Target</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>