|
Home > Archive > dBASE SQL Servers > February 2006 > Still getting started with SQL Server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Still getting started with SQL Server
|
|
|
| I read the Dbulletin article on SQL server that Jamie recommended in another post here. It is helpful, but I may need something even more basic.
I?m having trouble accessing a database, and am not sure if it is the BDE setup or if it is my lack of understanding of how SQL server works.
For example, after installing SQL Express on a local workstation and rebooting, I see from task manager that I have an instance of sqlsrvr.exe running as a network service. I assume this means I need do nothing else with SQL server, that it runs on bootup
, and I can work on the BDE to setup my databases.
I set up one using the native driver MSSQL, and another using ODBC. I run Plus and choose tables, and dropping down Look in I see the 2 databases I set up. I'm thinking...can it be this easy. But...
When I try to access the MSSQL based database, I get an error ? Vendor initialization failed NTWDBLIB.DLL.
When I try to access the ODBC database, I get an Open Database dialog box with the User Name I established and a place for a password. I have no idea of where I am to get this password.
Hope someone can help me figure this out. As you can see, I really am a true beginner.
Thanks for any ideas on this,
Rick
| |
|
| > When I try to access the MSSQL based database, I get an error ? Vendor
> initialization failed NTWDBLIB.DLL.
Don't use the native driver for MSSQL, it is for version 6.5.
> When I try to access the ODBC database, I get an Open Database dialog box
> with the User Name I established and a place for a password. I have no
> idea of where I am to get this password.
You should use the SQL Native Client driver from MS. When going thru the
wizard to setup the driver, on the page for how should sql server verify
authenticity for login choose With Integrated Windows Authentication. You
probably want to change the default database to the database that you are
using instead of the Master db.
HTH
Ken
| |
|
| Ken,
Thanks. This was really helpful. Making some progress, but am still unclear on at least 2 things:
1. The BDE parameter ODBC DSN - found in both the SQL Native Driver and in the database I set up. The help screen is not very much help. I?m guessing this should refer to some setting for SQL Express database, but not having much luck figuring this out.
I last set both the Native Driver and the database alias value for this parameter to AdventureworksDB, the name of the database I wish to practice on.
2. Also, the only interface to SQL Server Express seems to be the Server Management Studio Express. It is here where I found the option to use Windows Authorization option instead of SQL Server Authorization per your help in last post. This also gives m
e the option to connect or disconnect from the server.
If I connect, as you said, it seems to only have the master database available. Before connecting, I have an option to set the default database, along with network protocol, packet size, etc. You indicated I might want to change the default from master
to my database, which is the AdventureWorksDB Microsoft provides to practice with SQL Server. So I typed in AdventureworksDB here and connected.
I have tried several other entries here as well, but continue to get the error - ?Invalid configuration parameter? when I try to connect in Plus to the database I set up in the BDE. I assume this must refer to the BDE configuration, but the only changes
I made to the defaults were:
DatabaseName: C:\Program Files\Microsoft SQLServer\MSSQL. 1\MSSQL\Data\Adventu
reWorks_Data.mdf
LanguageDriver: 'WEurope' ANSI
ODBC DSN: AdventureworksDB
SQLQUERYMODE: Server
Sorry for this long post, but I still seem to be off base. Appreciate any additional advice you can give
Thanks,
Rick
Ken B Wrote:
> Don't use the native driver for MSSQL, it is for version 6.5.
>
>
> You should use the SQL Native Client driver from MS. When going thru the
> wizard to setup the driver, on the page for how should sql server verify
> authenticity for login choose With Integrated Windows Authentication. You
> probably want to change the default database to the database that you are
> using instead of the Master db.
>
> HTH
> Ken
>
>
| |
|
|
| Roland Wingerter 2006-02-25, 9:35 am |
| Hi Rick,
I installed SQL Server Express recently, and today for the first time tried
to connect from dBASE. Using ODBC I managed to connect to the server and see
the AdventureWorks database.
> If I connect, as you said, it seems to only have the master database
> available. Before connecting, I have an option to set the default
> database, along with network protocol, packet size, etc. You indicated I
> might want to change the default from master to my database, which is the
> AdventureWorksDB Microsoft provides to practice with SQL Server. So I
> typed in AdventureworksDB here and connected.
------
Can you see the AdventureWorksDB in SQL Server Management Studio? If not,
rightclick on "Databases" and choose "Attach..." and then the "Add..."
Pushbutton.
After that go to the ODBC administrator and go to the dialog where you can
change the default database. The AdventureWorks database should be available
in the dropdownlist. After saving the DSN entry and starting dBASE I can
open the database in the navigator (there is no password dialog). However,
when I try to open a table (e.g. HumanResource.Employee), I get án error
message "Alert - Error: Bad field type". :-( Perhaps this is one of the
problems Robert mentioned. I wonder if anyone else was successful opening
any of the tables in the AdventureWorks database?
Hope this helps a little bit.
Roland
| |
|
| > Thanks. This was really helpful. Making some progress, but am still
> unclear on at least 2 things:
You're welcome.
> 1. The BDE parameter ODBC DSN - found in both the SQL Native Driver and in
> the database I set up. The help screen is not very much help. I?m
> guessing this should refer to some setting for SQL Express database, but
> not having much luck figuring this out. I last set both the Native Driver
> and the database alias value for this parameter to AdventureworksDB, the
> name of the database I wish to practice on.
You only need to modify the AdventureWorks database alias under the Database
tab. Don't do anything to the drivers under the configuration tab.
> 2. Also, the only interface to SQL Server Express seems to be the Server
> Management Studio Express. It is here where I found the option to use
> Windows Authorization option instead of SQL Server Authorization per your
> help in last post. This also gives me the option to connect or disconnect
> from the server.
As far as I know, thats only for dealing with the Server Management Studio
Express application.
> If I connect, as you said, it seems to only have the master database
> available. Before connecting, I have an option to set the default
> database, along with network protocol, packet size, etc. You indicated I
> might want to change the default from master to my database, which is the
> AdventureWorksDB Microsoft provides to practice with SQL Server. So I
> typed in AdventureworksDB here and connected.
That's correct.
> I have tried several other entries here as well, but continue to get the
> error - ?Invalid configuration parameter? when I try to connect in Plus to
> the database I set up in the BDE. I assume this must refer to the BDE
> configuration, but the only changes I made to the defaults were:
>
> DatabaseName: C:\Program Files\Microsoft
> SQLServer\MSSQL. 1\MSSQL\Data\Adventu
reWorks_Data.mdf
Don't put in anything in the databaseName field. The ODBC DSN knows what
database it is working on.
> LanguageDriver: 'WEurope' ANSI
> ODBC DSN: AdventureworksDB
> SQLQUERYMODE: Server
You should be able to connect now. One thing you should know is that dbase
can't deal with some of the MSSQL fields in the AdventureWorks database. If
you try to double click on a table to browse it you will get an error
stating that there is a bad field type. Thats is because dbase can't deal
with the Unicode field types such as nvarchar and nchar, except for doing a
"Cast" function on the datatype to convert it to something dbase can use.
For example:
d = new database("adventureworks")
q = new query()
q.database := d
q.requestLive := false
q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
from 'humanresources.employee'"
q.active := true
You should create your own database and don't use those types to practice
on.
> Sorry for this long post, but I still seem to be off base. Appreciate any
> additional advice you can give
Hopefully, this will get you going.
| |
|
| Roland,
Ah, finally making serious progress thanks to the posts on this ng. Thanks to your info, I used attach and can actually see Adventrueworks in SSMSE! Boy it is great to finally see the database somewhere.
Under ODBC in the BDE, are you using the SQL Native Client or the SQL Server driver? I am assuming SQL Server.
When I right click on the SQL Server ODBC driver and choose ODBC administrator, I get the dialog where the first tab is User DSN. This tab lists out dBase, Excel, and Access but no other items. I do not see any default database option. Am I in the righ
t place?
There is an option to add another datasource. Is that what you did to get AdventureWorks to come up?
It looks like from other posts that the preferred driver is the native MSSQL driver and not ODBC. I will be trying to get that method up as well.
Thanks,
Rick
Roland Wingerter Wrote:
> Hi Rick,
>
> I installed SQL Server Express recently, and today for the first time tried
> to connect from dBASE. Using ODBC I managed to connect to the server and see
> the AdventureWorks database.
>
> ------
> Can you see the AdventureWorksDB in SQL Server Management Studio? If not,
> rightclick on "Databases" and choose "Attach..." and then the "Add..."
> Pushbutton.
>
> After that go to the ODBC administrator and go to the dialog where you can
> change the default database. The AdventureWorks database should be available
> in the dropdownlist. After saving the DSN entry and starting dBASE I can
> open the database in the navigator (there is no password dialog). However,
> when I try to open a table (e.g. HumanResource.Employee), I get án error
> message "Alert - Error: Bad field type". :-( Perhaps this is one of the
> problems Robert mentioned. I wonder if anyone else was successful opening
> any of the tables in the AdventureWorks database?
>
> Hope this helps a little bit.
>
> Roland
>
>
>
>
>
>
>
| |
|
| Ken,
Thanks again for your post. More questions I'm afraid below:
> You only need to modify the AdventureWorks database alias under the Database
> tab. Don't do anything to the drivers under the configuration tab.
Good to know.
>
>
>
> As far as I know, thats only for dealing with the Server Management Studio
> Express application.
>
Yes. Thanks to Roalnd's post, I am now able to see AdventureWorks in SSMSE.
>
> Don't put in anything in the databaseName field. The ODBC DSN knows what
> database it is working on.
OK, will try this.
>
> You should be able to connect now.
I think I am still having trouble getting the entry for ODBC DSN to be recognized. Per Roland's post, if the ODBC "sees" the Adventureworksdb it should be available in the drop down in the BDE. It is not, so I must be missing something. If I type it in
, when I go into Plus and attempt to see the bde alias Adventure1, I get a dialog and if I click ok, I get the Invalid configuration parameter error.
Any idea on what I am missing?
One thing you should know is that dbase
> can't deal with some of the MSSQL fields in the AdventureWorks database. If
> you try to double click on a table to browse it you will get an error
> stating that there is a bad field type. Thats is because dbase can't deal
> with the Unicode field types such as nvarchar and nchar, except for doing a
> "Cast" function on the datatype to convert it to something dbase can use.
> For example:
>
> d = new database("adventureworks")
> q = new query()
> q.database := d
> q.requestLive := false
> q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
> from 'humanresources.employee'"
> q.active := true
>
> You should create your own database and don't use those types to practice
> on.
Thanks for this tip. I have been reading about this issue, and good to get your heads up on it.
Thanks agian,
Rick
| |
|
| Thanks for your response, and especially for the gif displays. Gives me a better idea of what I may be doing wrong.
The Dbulletin article suggested to use the MSSQL native driver as well, but other posts here suggest Express 2005 (or more accurately SQL Server above 6.5) does not work with this native driver, so have been pursuing ODBC instead. If you are using it wit
h Express then I will try setting up both to see what results I get. Right now would just like to get at least one to work.
In regard to your MSSQL example, you have an entry for DATABASE NAME (rasmis) that does not indicate a path or specific file. The bde olh says this should be a path and file name so I entered (Using the browse button):
C:\Program Files\Microsoft SQLServer\MSSQL. 1\MSSQL\Data\Adventu
reWorks_Data.mdf
Am I messing this up here? What does your single entry refer to?
Really appreciate your help on this. Cannot seem to get my brain around how this setup is supposed to work.
Thanks again,
Rick
rb Wrote:
>
> THe error probably lies in the DSN, make sure that you have tested the DSN
> out, by clicking test connection
>
> Keep in mind that both native sql ling and ODBC have their drawbacks in
> dbase when it comes to MS SQL, cant remember them off hand, but I have
> generally used the Native links with great success.
> Also SQL EXPRESS has an updated driver, you would probably want to use that
> to conenct if connecting via ODBC. I have successfully connected to SQL
> Wxpress via the BDE native links.
>
> Hope it help
> Cheers
> Robert
>
>
>
| |
| Roland Wingerter 2006-02-25, 9:35 am |
| Rick wrote
>
> Ah, finally making serious progress thanks to the posts on this ng.
> Thanks to your info, I used attach and can actually see Adventrueworks in
> SSMSE! Boy it is great to finally see the database somewhere.
>
> Under ODBC in the BDE, are you using the SQL Native Client or the SQL
> Server driver? I am assuming SQL Server.
------
The BDE (Tab Configuration > Drivers > ODBC) lists all installed ODBC
drivers, among them "SQL Native Client" and "SQL Server".
Actually, both drivers work for me, but the "SQL Server" driver requires
some changes to the setup (see below). I should add that SQL Server is
installed locally, not on another machine.
Here are the steps to set up a DSN entry using "SQL Native Client" in the
ODBC Administrator.
1. Start the ODBC administrator (Start > Control Panel > Administration >
DataSources (ODBC); I am not sure about the
wording, as I use the German windows version).
2. In the ODBC Administrator, set up a new DSN (Data Source Name) for the
AdventureWorks database. I chose a System DSN, but I suppose a User DSN
should be okay too (Click Help button for an explanation of the differences
between System DSN and User DSN).
3. Click "Add..." button to add a new data source. Choose the "SQL Native
Client" driver and click "Finish".
4. This opens the dialog "Create an New Data Source to SQL Server". Here you
supply a name for the data source (e.g. AdventureWorksDB) and optionally a
Description. In the combobox next to "Server:", type
"<computername>\SQLEXPRESS". Click Next.
5. In the next page, click Next. This takes you to the screen where you can
change the default database. Mark the checkbox to "Change the default
database:", then click on the dropdownlist. This will display the available
entries, like "master", "model" etc. Use the vertical scrollbar to scroll
upwards and you should see the entry for "AdventureWorks". Choose that and
click Next again.
6. On the next page, choose "Finish". This will open the window "Microsoft
SQL Server Setup" displaying details for the newly created data source.
Click "Test Data Source" and there you are.
I hope this helps
Roland
********************
********************
********************
********************
*******
The following paragraph is quoted from this document:
http://msdn.microsoft.com/library/d...sseoverview.asp
[color=darkred]
There are some interesting dynamics based on the interactions between MDAC
providers and the SQL Native Client providers. For instance, MDAC 2.5, 2.7,
or 2.8 cannot connect to SQL Server 2005 using Shared Memory. This affects
any native application using SQL OLE DB or SQL ODBC and includes not only
existing native applications, but also the managed OLE DB or ODBC
applications since they use MDAC internally. Typically for SQL Server, if
the shared memory connection fails, networking protocols such as TCP/IP are
used. However, for SQL Server Express, since the networking is turned off by
default, these applications would simply fail to connect. The workaround is
to change the apps to use the SQL Native Client provider or enable
networking Transmission Control Protocol (TCP) and start SQL Browser.
<<
In order to configure SQL Server choose Start > Programs > Microsoft SQL
Server 2005 > Configuration Tools > SQL Server Configuration Manager.
In SQL Server Configuration manager, in the left pane, click on "SQL Server
2005 Services". The right pane will show the status for "SQL Server
(SQLEXPRESS)" and "SQL Server Browser". On my machine, both are running now.
You can start SQL Server Browser by rightclicking and choosing "Start" from
the context menu.
After that click on "SQL Server 2005 Network Configuration" > "Protocols for
SQLEXPRESS". In the right hand pane, click on TCP/IP and choose "Enable"
from the context menu. Then you can close the Server Configuration Manager.
| |
|
| Roland,
Yes!!! Set up using the ODBC Native client and now see all the AdventureWorks tables in Plus. Soooo good to actually see the tables in Plus. Now I can get started testing some code out.
Many thanks,
Rick
Roland Wingerter Wrote:
> Rick wrote
> ------
> The BDE (Tab Configuration > Drivers > ODBC) lists all installed ODBC
> drivers, among them "SQL Native Client" and "SQL Server".
>
> Actually, both drivers work for me, but the "SQL Server" driver requires
> some changes to the setup (see below). I should add that SQL Server is
> installed locally, not on another machine.
>
> Here are the steps to set up a DSN entry using "SQL Native Client" in the
> ODBC Administrator.
>
> 1. Start the ODBC administrator (Start > Control Panel > Administration >
> DataSources (ODBC); I am not sure about the
> wording, as I use the German windows version).
>
> 2. In the ODBC Administrator, set up a new DSN (Data Source Name) for the
> AdventureWorks database. I chose a System DSN, but I suppose a User DSN
> should be okay too (Click Help button for an explanation of the differences
> between System DSN and User DSN).
>
> 3. Click "Add..." button to add a new data source. Choose the "SQL Native
> Client" driver and click "Finish".
>
> 4. This opens the dialog "Create an New Data Source to SQL Server". Here you
> supply a name for the data source (e.g. AdventureWorksDB) and optionally a
> Description. In the combobox next to "Server:", type
> "<computername>\SQLEXPRESS". Click Next.
>
> 5. In the next page, click Next. This takes you to the screen where you can
> change the default database. Mark the checkbox to "Change the default
> database:", then click on the dropdownlist. This will display the available
> entries, like "master", "model" etc. Use the vertical scrollbar to scroll
> upwards and you should see the entry for "AdventureWorks". Choose that and
> click Next again.
>
> 6. On the next page, choose "Finish". This will open the window "Microsoft
> SQL Server Setup" displaying details for the newly created data source.
> Click "Test Data Source" and there you are.
>
> I hope this helps
>
> Roland
>
> ********************
********************
********************
********************
*******
> The following paragraph is quoted from this document:
>
> http://msdn.microsoft.com/library/d...sseoverview.asp
>
> There are some interesting dynamics based on the interactions between MDAC
> providers and the SQL Native Client providers. For instance, MDAC 2.5, 2.7,
> or 2.8 cannot connect to SQL Server 2005 using Shared Memory. This affects
> any native application using SQL OLE DB or SQL ODBC and includes not only
> existing native applications, but also the managed OLE DB or ODBC
> applications since they use MDAC internally. Typically for SQL Server, if
> the shared memory connection fails, networking protocols such as TCP/IP are
> used. However, for SQL Server Express, since the networking is turned off by
> default, these applications would simply fail to connect. The workaround is
> to change the apps to use the SQL Native Client provider or enable
> networking Transmission Control Protocol (TCP) and start SQL Browser.
> <<
>
> In order to configure SQL Server choose Start > Programs > Microsoft SQL
> Server 2005 > Configuration Tools > SQL Server Configuration Manager.
>
> In SQL Server Configuration manager, in the left pane, click on "SQL Server
> 2005 Services". The right pane will show the status for "SQL Server
> (SQLEXPRESS)" and "SQL Server Browser". On my machine, both are running now.
> You can start SQL Server Browser by rightclicking and choosing "Start" from
> the context menu.
>
> After that click on "SQL Server 2005 Network Configuration" > "Protocols for
> SQLEXPRESS". In the right hand pane, click on TCP/IP and choose "Enable"
> from the context menu. Then you can close the Server Configuration Manager.
>
>
>
| |
|
| Ken,
Many thanks for your input on this. Now have a bde alias set up that works in plus and uses the ODBC Native Client.
Whew...did not find this easy. Next step is to start trying out some code.
Thanks again,
Rick
Ken B Wrote:
> You're welcome.
>
> You only need to modify the AdventureWorks database alias under the Database
> tab. Don't do anything to the drivers under the configuration tab.
>
>
>
> As far as I know, thats only for dealing with the Server Management Studio
> Express application.
>
>
> That's correct.
>
>
> Don't put in anything in the databaseName field. The ODBC DSN knows what
> database it is working on.
>
> You should be able to connect now. One thing you should know is that dbase
> can't deal with some of the MSSQL fields in the AdventureWorks database. If
> you try to double click on a table to browse it you will get an error
> stating that there is a bad field type. Thats is because dbase can't deal
> with the Unicode field types such as nvarchar and nchar, except for doing a
> "Cast" function on the datatype to convert it to something dbase can use.
> For example:
>
> d = new database("adventureworks")
> q = new query()
> q.database := d
> q.requestLive := false
> q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
> from 'humanresources.employee'"
> q.active := true
>
> You should create your own database and don't use those types to practice
> on.
>
>
> Hopefully, this will get you going.
>
>
>
| |
|
| rb,
Thanks for your help on this. Got a successful ODBC Native Client connection and ready to start with some code.
Sure do appreciate all the help people gave here. I'd be lost without these ngs.
Rick
rb Wrote:
> HI,
>
> "Rick" <Sportman7@netzero.com> wrote in message
> news:r6jjaeCOGHA.1152@news-server...
> unclear on at least 2 things:
> the database I set up. The help screen is not very much help. I?m guessing
> this should refer to some setting for SQL Express database, but not having
> much luck figuring this out. I last set both the Native Driver and the
> database alias value for this parameter to AdventureworksDB, the name of the
> database I wish to practice on.
>
> You should not have the ODBC option under the native SQL driver. If you do
> then you are using the wrong driver. Attached is two gif files showing the
> difference between the native and odbc setups in the bde
>
>
> Management Studio Express. It is here where I found the option to use
> Windows Authorization option instead of SQL Server Authorization per your
> help in last post. This also gives me the option to connect or disconnect
> from the server.
> available. Before connecting, I have an option to set the default database,
> along with network protocol, packet size, etc. You indicated I might want
> to change the default from master to my database, which is the
> AdventureWorksDB Microsoft provides to practice with SQL Server. So I typed
> in AdventureworksDB here and connected.
> error - ?Invalid configuration parameter? when I try to connect in Plus to
> the database I set up in the BDE. I assume this must refer to the BDE
> configuration, but the only changes I made to the defaults were:
> SQLServer\MSSQL. 1\MSSQL\Data\Adventu
reWorks_Data.mdf
>
> THe error probably lies in the DSN, make sure that you have tested the DSN
> out, by clicking test connection
>
> Keep in mind that both native sql ling and ODBC have their drawbacks in
> dbase when it comes to MS SQL, cant remember them off hand, but I have
> generally used the Native links with great success.
> Also SQL EXPRESS has an updated driver, you would probably want to use that
> to conenct if connecting via ODBC. I have successfully connected to SQL
> Wxpress via the BDE native links.
>
> Hope it help
> Cheers
> Robert
>
>
>
| |
| Roland Wingerter 2006-02-25, 9:35 am |
|
Rick wrote
>
> Yes!!! Set up using the ODBC Native client and now see all the
> AdventureWorks tables in Plus.
-----
Rick,
I am glad to hear it works.
> Many thanks,
-----
Thank you for starting this thread, and thanks to Ken B. and Robert. Without
your questions and their input I probably wouldn't have tried.
Roland
| |
| Roland Wingerter 2006-02-25, 9:35 am |
| Hi Ken,
"Ken B" wrote
>
> One thing you should know is that dbase can't deal with some of the MSSQL
> fields in the AdventureWorks database. If you try to double click on a
> table to browse it you will get an error stating that there is a bad field
> type. Thats is because dbase can't deal with the Unicode field types such
> as nvarchar and nchar, except for doing a "Cast" function on the datatype
> to convert it to something dbase can use. For example:
>
> d = new database("adventureworks")
> q = new query()
> q.database := d
> q.requestLive := false
> q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
> from 'humanresources.employee'"
> q.active := true
-------
Thanks for pointing this out. I have done the setup and can see the tables
in the dBASE navigator. However, when I run this code form a *.prg file, I
receive the following error:
Server Error: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax
near 'humanresources.employee'.
Any ideas? Just curious.
> You should create your own database and don't use those types to practice
> on.
-------
Thanks for your advice. I already spent more time playing around than I can
afford. ;-)
Roland
| |
|
|
|
| >> d = new database("adventureworks")
> -------
> Thanks for pointing this out. I have done the setup and can see the tables
> in the dBASE navigator. However, when I run this code form a *.prg file, I
> receive the following error:
>
> Server Error: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax
> near 'humanresources.employee'.
>
> Any ideas? Just curious.
remove the single quotes around the humanresources.employee table name. As
such:
q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
from humanresources.employee"
| |
|
| > Many thanks for your input on this. Now have a bde alias set up that
> works in plus and uses the ODBC Native Client.
>
> Whew...did not find this easy. Next step is to start trying out some
> code.
>
> Thanks again,
> Rick
You're welcome. Remember that the q.requestLive := false property makes the
rowset readonly and you will not be able to edit records directly like a
normal rowset.
Ken
| |
|
| Robert,
Thanks for the info. My initial task is to use plus to extract name and address info from an SQL server database. In exploring AdventureWorks, I do see a wide variety of field types I do not always recognize. I know you mentioned nvarchar can cause a p
roblem for reading with plus without casting it.
Since I must deal with a fairly unknown SQL server database, I will first need to uncover the table structures (probably with SSMSE). Do you have a list you have used from your experience that indicates the other field types that could need casting when u
sing BDE to access them?
Also, the Books On Line is huge. Can you recommend any reference that might give me basic info explaining SQL server field types other than the BOL?
Thanks again for your help on this,
Rick
rb Wrote:
> HI,
>
> "Rick" <Sportman7@netzero.com> wrote in message
> news:kV8oRVLOGHA.592@news-server...
> better idea of what I may be doing wrong.
> but other posts here suggest Express 2005 (or more accurately SQL Server
> above 6.5) does not work with this native driver, so have been pursuing ODBC
> instead. If you are using it with Express then I will try setting up both
> to see what results I get. Right now would just like to get at least one to
> work.
> I've mange to get MSQL2000 to work with BDE native SQL Links, with some
> minor issues, but overall, I am using the Native SQL Links.
> I did try theSQLExpress cant remember if it was with the native links or
> ODBC, I would have to chaeck at home, but anyway, It worked flawlessly, so
> far in dbase
>
> (rasmis) that does not indicate a path or specific file. The bde olh says
> this should be a path and file name so I entered (Using the browse button):
> SQLServer\MSSQL. 1\MSSQL\Data\Adventu
reWorks_Data.mdf
> how this setup is supposed to work.
>
>
> That single entry is the database. SQL Server takes care of the path, which
> points to the database data files. In fact you could have these datafiles
> and log files split of different drives and locations See the two attached
> gif files for an example.
> The full path is normally used with servers such as Interbase, Firebird,
> MySQL.
> And that would normally be used with ODBC connections, and only if you
> havent already supplied the database name in the DSN. Once again, don't get
> the two confused. Native LInks is just that, it connects directly to the SQL
> server. ODBC goes through another layer, then connects to the server.
>
> Robert
>
>
> Robert
>
>
| |
|
| Ken,
Thanks to you and others here, am getting into the task. Was wondering if you have a list of SQL server field types besides nvarchar that can give problems reading with plus? Also, any references besides books on line you use to better understand SQL se
rver?
Thanks again,
Rick
Ken B Wrote:
> You're welcome. Remember that the q.requestLive := false property makes the
> rowset readonly and you will not be able to edit records directly like a
> normal rowset.
>
> Ken
>
>
| |
|
| > Thanks to you and others here, am getting into the task. Was wondering if
> you have a list of SQL server field types besides nvarchar that can give
> problems reading with plus? Also, any references besides books on line
> you use to better understand SQL server?
Any data type with the "n" as the first letter is all that I know of. I
have only played around with the data.
| |
| Roland Wingerter 2006-02-25, 9:36 am |
| "Ken B" wrote
> remove the single quotes around the humanresources.employee table name.
> As such:
> q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
> from humanresources.employee"
------
Tried that, but it gives a different error:
Server Error: [Microsoft][SQL Native Client][SQL Server]Invalid object name
'humanresources.employee'.
Does it work for you? Then it could be something in my setup...
Roland
| |
| Robert Bravery 2006-02-25, 9:36 am |
|
"Rick" <Sportman7@netzero.com> wrote in message
news:PmFsHiWOGHA.2016@news-server...
> Robert,
>
> Thanks for the info. My initial task is to use plus to extract name and
address info from an SQL server database. In exploring AdventureWorks, I do
see a wide variety of field types I do not always recognize. I know you
mentioned nvarchar can cause a problem for reading with plus without casting
it.
>
> Since I must deal with a fairly unknown SQL server database, I will first
need to uncover the table structures (probably with SSMSE). Do you have a
list you have used from your experience that indicates the other field types
that could need casting when using BDE to access them?
I would have to go back and check.
> Also, the Books On Line is huge. Can you recommend any reference that
might give me basic info explaining SQL server field types other than the
BOL?
>
Try this
http://www.databasejournal.com/feat...le.phpr/2212141
Perhaps you could post some DDL of the tables and or database, then we can
work through it to help
Robert
| |
|
| This works for me:
clear all
clear
d = new database("adventureworks")
q = new query()
q.database := d
q.requestLive := false
q.sql := "select employeeid, cast('maritalStatus'
as char(1)) as married
from humanresources.employee"
q.active := true
i = 0
do while not q.rowset.endofset
? i++
for fld = 1 to q.rowset.fields.size
?? " " + q.rowset.fields[fld].value
next fld
q.rowset.next()
enddo
q.active := false
d.active := false
| |
| Roland Wingerter 2006-02-28, 8:25 pm |
| "Ken B" wrote
> This works for me:
-------
Thanks, Ken.
Tried again and found out that it works for me too, but only when I use
exact case in the SQL statement ('select EmployeeID etc.' works, but not
'select employeeid etc.')
This must be a setting in the ODBC DSN set up. I unchecked "ANSI Quoted
Identifiers" but it did not make a difference. Can you tell me where to
look?
Roland
| |
|
| > Tried again and found out that it works for me too, but only when I use
> exact case in the SQL statement ('select EmployeeID etc.' works, but not
> 'select employeeid etc.')
>
> This must be a setting in the ODBC DSN set up. I unchecked "ANSI Quoted
> Identifiers" but it did not make a difference. Can you tell me where to
> look?
No. Try recreating the DSN and just accept the defaults.
| |
| Robert Bravery 2006-02-28, 8:25 pm |
| HI,
yes there is a difference between ODBC and BDE Native sql links. One is case
sensitive, the other is not. Cant remember which, but it would seem that it
is the native links, as I use this at works, and am mondfull of the case.
Robert
"Roland Wingerter" <ich@hier.de> wrote in message
news:1hJSdtiOGHA.1740@news-server...
> "Ken B" wrote
> -------
> Thanks, Ken.
>
> Tried again and found out that it works for me too, but only when I use
> exact case in the SQL statement ('select EmployeeID etc.' works, but not
> 'select employeeid etc.')
>
> This must be a setting in the ODBC DSN set up. I unchecked "ANSI Quoted
> Identifiers" but it did not make a difference. Can you tell me where to
> look?
>
> Roland
>
>
>
| |
| Roland Wingerter 2006-02-28, 8:25 pm |
| "Ken B" wrote
> No. Try recreating the DSN and just accept the defaults.
------
I did that. Still the same. On my machine, table and field names in the
AdventureWorksDB are case sensitive. Not only when I try from dBASE, but
also in Microsoft SQL Server Management Studio Express.
Anyway, I can live with that.
Thank you for your help.
Roland
| |
| Roland Wingerter 2006-02-28, 8:25 pm |
| Robert Bravery wrote
>
> yes there is a difference between ODBC and BDE Native sql links. One is
> case
> sensitive, the other is not. Cant remember which, but it would seem that
> it
> is the native links, as I use this at works, and am mondfull of the case.
------
Robert,
Seems that SQL Express itself or the AdventureWorks database is case
sensitive. Oh well, I can live with that.
Thanks for your help.
Roland
| |
| Robert Bravery 2006-02-28, 8:25 pm |
| HI Roland,
No not sql express, the driver used. I just tested with SQL Express and all
wortks fine.
tried:
select firstname from test
select Firstname from test
select FirstName from test
Test has two columns FirstName, LastName
My odbc settinga are
Microsoft SQL Native Client Version 09.00.1399
Data Source Name: RASSQLSERVERNATIVECL
IENT
Data Source Description:
Server: ROBERT\SQLEXPRESS
Use Integrated Security: Yes
Database: test
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: Yes
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Robert
"Roland Wingerter" <ich@hier.de> wrote in message
news:6AhzImsOGHA.1148@news-server...
> Robert Bravery wrote
case.[color=darkred]
> ------
> Robert,
>
> Seems that SQL Express itself or the AdventureWorks database is case
> sensitive. Oh well, I can live with that.
>
> Thanks for your help.
>
> Roland
>
>
| |
|
|
| Roland Wingerter 2006-02-28, 8:25 pm |
| Hi Robert,
"Robert Bravery" <me@u.com> schrieb im Newsbeitrag
news:A8jOBNuOGHA.560@news-server...
> HI Roland,
> No not sql express, the driver used. I just tested with SQL Express and
> all
> wortks fine.
> tried:
> select firstname from test
> select Firstname from test
> select FirstName from test
>
> Test has two columns FirstName, LastName
>
> My odbc settinga are
> Microsoft SQL Native Client Version 09.00.1399
------
I use the same driver as you. The new database I created is not case
sensitive. However, when I add a new table to AdventureWorksDB, it is case
sensitive. Again, I used the same driver as you. It is a mystery, but not
important for now.
Thanks again for your help.
Roland
| |
|
| Robert,
Thanks. The article is helpful and much more succiinct than the bol.
RE posting, just getting started. Have not received the db from the client yet, but thought I would try things out first with AdventureWorks.
Thanks,
Rick
Robert Bravery Wrote:
>
> "Rick" <Sportman7@netzero.com> wrote in message
> news:PmFsHiWOGHA.2016@news-server...
> address info from an SQL server database. In exploring AdventureWorks, I do
> see a wide variety of field types I do not always recognize. I know you
> mentioned nvarchar can cause a problem for reading with plus without casting
> it.
> need to uncover the table structures (probably with SSMSE). Do you have a
> list you have used from your experience that indicates the other field types
> that could need casting when using BDE to access them?
>
> I would have to go back and check.
>
> might give me basic info explaining SQL server field types other than the
> BOL?
>
> Try this
> http://www.databasejournal.com/feat...le.phpr/2212141
>
> Perhaps you could post some DDL of the tables and or database, then we can
> work through it to help
>
> Robert
>
>
>
|
|
|
|
|