P9 and MS Sql Server
Jim Giner
I am wondering - can P9 be used to write reports against a MS SQL Server
database? We're in the process of migrating from Paradox (and other
formats) to SQL Server and don't yet have a great reporting tool, so I was
wondering if I can get a driver that will work with P9 so I can create
ad-hoc reports in paradox that retrieve live data from our SQL server. I
know that I can export sql data into a paradox format, but I'd really prefer
being able to read the sql data directly.
Larry DiGiovanni
Jim Giner wrote:
> I am wondering - can P9 be used to write reports against a MS SQL
> Server database?
Yes, I do it all the time. You just need to create an ODBC DSN to the MS
SQL Server database and enable Auto ODBC in the BDE, then the DSN will
appear to Paradox as a BDE Alias. You can open, query, and run passthre
SQL against the SQL Server tables, and include the tables in Reports.
Best to run passthru SQL against remote tables and base the report on an
Answer table.
Also note that Paradox cannot recognize Unicode types (ntext, nvarchar,
nchar, etc), so if those types are used on your SQL Server database, you
will have to translate them. You can either do this on the fly with
Passthru SQL or base your queries, etc on SQL Server views. In either
case, re-project any unicode types as the same name, but with the
CONVERT() function. (I think that's the function).
--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources
Jim Giner
Thanks for the info - I hope I can digest it all.
I opened up the bde configuration utility and selected the SQL Server, but I
don't see anything called "autoodbc". What should I be looking for?
"Larry DiGiovanni" <nospam@nospam> wrote in message news:415316a6_3@cnews...
Jim Giner
> Yes, I do it all the time. You just need to create an ODBC DSN to the MS
> SQL Server database and enable Auto ODBC in the BDE, then the DSN will
> appear to Paradox as a BDE Alias. You can open, query, and run passthre
> SQL against the SQL Server tables, and include the tables in Reports.
>
OK - I believe that I have an odbc connection established in my control
panel/admin tools/odbc admin. (I'm running XP). In the O... Admin panel I
created something under the User DSN tab that uses the SQL Server driver. I
also created something under the System DSN tab that uses a SQL Server
driver.
Neither of the names associated with these two "odbc connections"(?) shows
up in my list of aliases when I open P9. I did go into the BDE Config.
manager and both of these names show up there, and I noted that the name of
the .CFG file that I am seeing is the one I open my pdox session up with (I
even added the -o switch to make sure). What am I missing?
Larry DiGiovanni
In the BDE Config, the setting is found on the Configuration tab, under
System|Init. It is the first option "AUTO ODBC" and it must be TRUE. If
not, change it, save the config, and restart Paradox.
If that doesn't do it, try creating the DSN as a System DSN.
Jim Giner
Ok - now we're cooking with gas. So here I am:
Tried to build a query in the old fashioned pdox way but that didn't work
("capability not supported"). So now I'm in the Visual Query Builder and I
clicked on the two dropdowns in the menu bar to selecte my alias and a table
within that alias on my sql server. A panel shows up in the Query Builder
window showing field names from the table - but not very many of them. Only
a couple of date/time fields and a couple of dollar amounts that are typed
(apparently) as "float". Where are my char. fields that comprise most of
the data? I actually selected a couple of these fields and let her run and
it came back with results, but without the char fields that describe the
records, this isn't much good. Is this the conversion problem you first
mentioned? how do I correct this part now?
Thanks in advance!!!!
"Larry DiGiovanni" <nospam@nospam> wrote in message news:415325db_1@cnews...
Bertil Isberg
Larry
<<
It is the first option "AUTO ODBC" and it must be TRUE
>>
AutoODBC =TRUE works well when you have just a few ODBC drivers, and no
mandatory ODBC DSNs installed for all users in an organization.
In short, I have found too many cases with AutoODBC=TRUE and also Options =
Virtual, so I know don't use it anymore.
Typical errors:
Could not write to engine configuration file (Paradox alias manager - not
BDE Admin)
Unknown table type (Saving or running a QBE based on remote table)
--
Bertil Isberg - CTECH
Paradox buglist:
online: http://w1.826.comhem.se/~u82608896/
FAQ newsgroup: corel.wpoffice.paradox-faq
Larry DiGiovanni
Jim Giner wrote:
> Tried to build a query in the old fashioned pdox way but that
> didn't work ("capability not supported").
Do you have Unicode columns in the tables? You can only see this using a
native SQL Server tool, like QueryAnalyzer. If that's the case, refer
back to my original posting for a workaround.
> So now I'm in the Visual Query Builder and I
I've never used the VQB, so I can't help much there.
> Is this the conversion problem you first mentioned?
I suspect it is.
> how do I correct this part now?
You'll have to project affected columns in your SQL Server tables using
the T-SQL function CONVERT()
Example,
mytable:
Reckey I (PK)
Descr nvarchar(30)
If what you want is:
SELECT Reckey, Descr
FROM mytable
You'd write it as:
SELECT Reckey, CONVERT(varchar, Descr) AS Descr
FROM mytable
You can still do comparisons, etc on the unaltered column:
SELECT Reckey, CONVERT(varchar, Descr) AS Descr
WHERE Descr = 'foo'
I.e., you don't have to CONVERT columns for comparisons (so long as you
ensure the query runs remotely).
This can get a little ponderous and isn't helpful if you want to QBE, or
bind forms or reports, so a workaround is to have someone (the DBA) create
VIEWS of all of the tables you want to get at that employ the CONVERT()
function as above:
CREATE VIEW mytable_v AS
SELECT Reckey, CONVERT(varchar, Descr) AS Descr
FROM mytable
Then you can work directly with mytable_v as you would a Paradox table,
more or less.
Note that I may have gotten the syntax on CONVERT wrong. Check BOL to be
sure.
Larry DiGiovanni
On this particular machine, I have 83 aliases, more than half of which are
ODBC to Oracle, MS SQL, IB, and MySQL. I don't know about errors, but it
sure is inconvenient. :-)
The workaround, Jim, is to create a new Alias, choose the type as the ODBC
Driver, and specify the DSN.
Bertil Isberg
Larry
<<
On this particular machine, I have 83 aliases, more than half of which are
ODBC to Oracle, MS SQL, IB, and MySQL.
>>
I had some 30 drivers under Drivers - ODBC. When I removed them, I got rid
of the errors. But as soon as AutoODBC is set to TRUE, the ODBC drivers
reoccur. I rather believe it's the number of drivers than the number of
aliases.
I have spent a couple of days at my office to find the explanation and
solutions for those errors.
Larry DiGiovanni
Nice catch. Thanks for the heads up.
Jim Giner
Thanks for the help so far. I went in and built my query using the Query
Expert instead and that showed me 'all' the field names. So - I built a
query. It didn't work very well the first time, so I edited it and cleaned
it up a little - there were some words that didn't have spaces between them
(?) and I fixed all that up. Now when I run it, it gives me a login box and
I supply my user/pswd info. Then it returns an error "type mismatch in
expression" which must have to do with vchar fields or something. Have to
wait for my dba to get in and go over the sql field defs and try your
'conversion' logic.
I just may make headway today!
"Larry DiGiovanni" <nospam@nospam> wrote in message
news:41532db7$1_2@cnews...
Jim Giner
OK - discussed the field types with my dba and made the following changes to
my query.
SELECT cast(dbo_pbfdtl.proj as char(6))
FROM ":SQL_PBF:dbo.PBFDTL" dbo_PBFDTL
Now the query was originally built by the Query Expert - I just chopped it
down to the bare minimum for testing this out. When I attempt to run it
the error I get from this is:
(from paradox)
"Type mismatch in expression"
Now - I've played with the syntax enough to believe that I don't have a
syntax error, since I get to the login box before I get the error message.
When my syntax was incorrect (such as when I was trying to use "convert"
instead of cast) I didn't get that far along when I hit F8. So - I have to
think it's something to do wiht the data conversion. The SQL table has the
field "proj" stored as "nchar". I'm trying to convert it to a paradox alpha
field, but "alpha" is not accepted syntactically - "char" is though.
Anybody have an idea as to what my error is and how to correct this? I'm
really excited to think that I can still use paradox to generate reports off
my new database, so I really want to clear this hurdle.
Bertil Isberg
Jim
<<
SELECT cast(dbo_pbfdtl.proj as char(6))
FROM ":SQL_PBF:dbo.PBFDTL" dbo_PBFDTL
>>
By supplying the alias in the SQL statement, the SQL will run locally,
and Paradox cannot handle nchar(). You have to run it remotely by
removing the alias, and instead selecting the Alias :SQL_PBF: in the
alias control on the toolbar. When you do, note that the Title of the
SQL Editor now will say :SQL_PBF; instead of :WORK:
SELECT cast(proj as char(6)) as Proj
FROM dbo.PBFDTL
The last as Proj is to give the column a better name in Paradox.
--
Bertil Isberg
CTECH
FAQ newsgroup: corel.wpoffice.paradox-faq
Paradox Buglist: http://w1.826.comhem.se/~u82608896/
remove spamfilter (reversed) to reply
Jim Giner
I changed my query as you suggested. I changd the only dropdown box that
would allow me access to show the sql_pbf alias in it (it had been WORK).
Now when I run it I get "Table Does Not Exist", followed by (>) a MS SQL
Server driver error saying essentially "Invalid Object name 'dbo_PBFDTL'"
I thought when I checked the properties of the query, under the SQL table,
and selected the "run query remotely" option, that I WAS running the query
remotely. This does not work that way?
"Bertil Isberg" <bertil...@retlifmapsSCB.se> wrote in message
news:41541ac7$1_3@cnews...
Jim Giner
Eureka!!
I removed the "dbo." from the FROM clause and it ran. So - where does that
'dbo' come from and what's it mean?
"Bertil Isberg" <bertil...@retlifmapsSCB.se> wrote in message
news:41541ac7$1_3@cnews...
>
Bertil Isberg
Jim
The statement should be
SELECT cast(proj as char(6)) as Proj
FROM dbo.PBFDTL
when run at the server. IMO the dbo should be there. It's the owner of
the table.
<<
I thought when I checked the properties of the query, under the SQL
table, and selected the "run query remotely" option, that I WAS running
the query remotely. This does not work that way?
>>
If you write the SQL in a correct way, it will work. It won't remove the
Paradox specific notation like an alias.
Jim Giner
Well, it works ONLY when I remove the "dbo.", so I guess I won't tamper with
that.
Thanks for the push in the right direction- at least I can now get to my sql
server data. Now onwards to more complex queries.
"Bertil Isberg" <bertil...@retlifmapsSCB.se> wrote in message
news:41542335_2@cnews...
Larry DiGiovanni
Jim Giner wrote:
> Well, it works ONLY when I remove the "dbo.", so I guess I won't
> tamper with that.
Post the SQL that worked and the SQL that didn't work. Are you connecting
to the database as the database owner or dba/sa?
Bertil Isberg
Larry
Could this be caused by the ODBC setting Quoted Identifiers?
Jim Giner
Actually it was caused by the Query Expert - the QE had produced the code as
dbo_pbfdtl - when my dba (and sql expert) saw it he realized that it should
have been dbo.pbfdtl and that now works.
I'm a very happy man today. Thanks all!!!
"Bertil Isberg" <bertil.isberg@NOcomhemSPAMdotse> wrote in message
news:41543ac4$1_2@cnews...
Larry DiGiovanni
Bertil Isberg wrote:
> Could this be caused by the ODBC setting Quoted Identifiers?
That's why I wanted to see working/nonworking code. Some of the snippets
he'd posted made me wonder about the generated SQL.
But since Jim is now a very happy man, our work here is done. <g>