Flat Files
-
Hi,
I've never used SQL Server, but I'm considering it for a new app I want to develop. I'm an Oracle guy and I'm used to using SQL Plus to create flat files, among other things (it's really got a lot of sophisticated report writing capabilities built-in). Is there anything comparable for SQL Server?
Thanks,
Steve
-
Not particularly familiar with Oracle and SQL Plus. What is the purpose of the flat files that you're creating?
--------------------
Colt 45 - the original point and click interface
-
Hi,
SQL+ is basically a general purpose command line front end to the Oracle database. It also has extended functionalities that let you create text based reports. By setting some parameters and spooling the results, you can create flat files (actually now, they've added functionality to create HTML, but I don't know how to use it).
The app will be in the clinical trial space. Right now, it is pretty much strictly Unix and Oracle. Pretty sure I'm going to create that version, but thought that SQL Server might be a good option for smaller pharmas that can't afford Oracle.
Steve
-
If the flat files are for data transfer purposes then DTS is your tool. For really, really basic text file output you can use the OSQL command-line utility.
If a report is more like what you're after, then SQL Server also has Reporting Services which is a full featured reporting tool.
All this functionality is included with the initial SQL Server license.
--------------------
Colt 45 - the original point and click interface -
Thanks. Does DTS do things like column aliasing, summaries, counts, etc., or is it just a way to dump query output? And the reporting services, how do they transfer data?
The closest thing that I am thinking of with SQL+ is SAS's PROC PRINT. Those are reports (look at the tables in your next prescription drug package insert, they are just PROC PRINT output cut and pasted into the document
) From what your telling me, I'm guessing that DTS is the closest thing.Thanks again,
Steve
-
If you really need a flat file and DTS is not suitable then the bcp utilily might be worth a look. It is quite powerful. You can use it for import as well as export.
To quote BOL:
"The bcp utility copies data between an instance of Microsoft® SQL Server™ 2000 and a data file in a user-specified format"
-
h!!,
Similar to SQL loader in oracle SQL server has DTS which can be used efficetively for transfering data across RDBMS with ease .Only disadvantage of DTS is it won't be transfering constraints or any other database objects when connected with oracle .Moreover through DTS you can query the source table to filter the result set which should be transfered.
regards,
Vinod (DBA)
-
Hi,
This is the kind of thing that SQL+ can do with the results of a single query:
A C M E W I D G E T
EMPLOYEE REPORT PAGE: 1
DEPARTMENT LAST NAME MONTHLY SALARY
---------- ------------------------- --------------
20 Hartstein $13,000
********** --------------
sum $13,000
80 Russell $14,000
Partners $13,500
********** --------------
sum $27,500
90 King $24,000
Kochhar $17,000
De Haan $17,000
********** --------------
sum $58,000
--------------
sum $98,500
COMPANY CONFIDENTIAL
From what I'm gathering, bcp functionality is going to be the closest. Is that right?
Again, thanks so much for your help,
Steve
-
BCP (Bulk Copy Program) can (among other things) create a text file with the results of any query you want to write (although it is easier if you put the query in a view). DTS (Data Transformation Services) can move data from almost any source (flat file, Excel, Access, SQL Server, Oracle, any other ODBC or OLE DB connection) to any other, with all kinds of transforms and lookups and other processing in between. Neither of them are "reporting tools" in the sense of giving you a simple interface for formatting and grouping the output.
Reporting Services is what you're looking for, in SQL 2005 it will be roughly on a level (in terms of ease of use, IMHO) with creating reports in Access. I haven't used the current version of Reporting Services but I've heard it has limitations (especially lousy print functionality).
If you needed this ability right away, I would use an Access front end with links to all the SQL Server tables, and build the reports in Access, or possibly something like Crystal Reports. Either way you could develope reports that would work with either SQL Server or Oracle, or most other database solutions.
-
Hi,
I expect that most of the functionality that I will require would be covered by BCP; since I need text based reports, perhaps, with some real effort, DTS could be squeezed to provide the extra functionality needed. I don't think the Reporting Services would help, since that seems to be very GUI based.
Now, it's a question of market research as to which (or both) platforms to pick!
Thanks once again for your help,
Steve
-
Hi Stephen,
I doubt there is anything in SQL Server as comparable to Oracle's report generation via SQL*Plus. I know its very quick and easy to build such a report in SQL*Plus and then save the script to a file, but unfortunately the only way to do something that could come close to that is using plain old stored procedures in SQL Server, but then it is compiled and so is faster than your Oracle report script.
Of course, there is the SQL Server Reporting Services, but then that is comparable to Oracle SQL*Reports.
Best regards,
Tony John.
-
Hi Tony,
Thanks. Execution speed really isn't an issue with my app, so that's not a problem with SQL+. But how would you duplicate the functionality with stored procedures? Is there a way in TSQL (?) to write to a file on the server? Would you just have to build everything manually within the stored procedures, or are there packages available to help you do that?
Thanks,
Steve
-
I have been doing dual development on Oracle and SQL Server for about three years now, usually starting with a good SQL*Plus script and then converting it to T-SQL and DTS.
DTS is the way to generate the output. For output, pick a text file and use comma delimiters and quotes for text. This results in a simple CSV file. Excel or other document types can be used, but they are a bit more tricky and not worth the effort. CSV is as good and much simpler.
As for the aggregates, I found that the best way to do this is to create a stored procedure that generates all the output rows, including aggregates with GROUP BY and ROLLUP. You can use temporary tables to simplify the steps. You should use the GROUPING function to create extra columns for ordering the final output so that the aggregates come out in the correct place. My users wanted totals at the top of the details so this was especially necessary. These extra columns need not be included in the select's column list.
The DTS merely has a connection, a data transformation task that execs the stored proc, and the text file output. I also use a dynamic properties object and parameterize the stored procedure so that the date range or other query parameters can change.
-
Hi,
Thanks Peter. The scripts will not be pre-programmed by the app, but will be created by a programmer and then called by the app. If I understand you correctly, SQL+ provides a language for a programmer to use to generate reports, but you need to do that programming yourself with DTS. Basically, 9 of one, quarter dozen of another.
I'll give you guys a tip: using the 0x7F character as a delimiter. Other good delimiters are the 0xA0 and the 0xFF characters are good, while the ` and the ~ work very well in almost all circumstances as well.
Also, seems like there is a gap in the SQL Server functionality--someone could make some money duplicating the SQL+ functionality for SQL Server.
Steve
-
There are differences, but there are also many similarities. You might get similar results from using OSQL and the proper PRINT statements or even SELECT. Microsoft also has COMPUTE BY. I never really explored that avenue because it wasn't mainstream, even deprecated, with SQL Server, while DTS is definitely mainstream.
The usual delimiters, quotes and commas, are a real problem when it comes to certain data. You can't use these to dump formatted text from a database.
Do your delimiters work ok with importing data into Excel or other user applications or do you process these "reports" with another program before presenting them to end users?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply