[WD16] Create SQL server files from HF Classic files

Startbeitrag von Al am 03.06.2011 05:43

Hello All

We are trying to create copies of our HF Classic files in SQL server so that it can be used in an external BI application.

Is there a quick way of creating the SQL server files in our analysis? There are over 70 files and I am trying to build an analysis with both sets of files in it.

Regards
Al

Antworten:

Hello All

Found the SQL script generator and have created the files - new problem now.

I am not sure what goes into the connection fields.
I am using SQLServer express on a Windows 2003 server.
I have the Native Access program from PCSoft.
The database is buried inside the program files directory

When I am in the analysis trying to create a connection what goes in the data Source field ?

I have tried many combinations of server name, ip address etc
The database server name is ANSERVER\SQLEXPRESS and I am using windows authentication

Whatever I try I always get the same type of error
Connection to database failed
Unable to connect to 'ANSERVER\SQLEXPRESS' database - this is odd because it is the name I am putting in the "DataSource" field. The name of the database is "RptDB"
Why doesn't it say I can't connect to the database name I entered rather than the name I enter in the source ?

Native SQLServer access error
Error number = 117

Error 80004005 returned by SQLServer
[DBNETLIB][ConnectionOpen[Connect()).]SQL Server does not exist or access denied
SQL State: 08001
SQL Error Number 17

Any help greatly appreciated

Regards
Al



von Al - am 03.06.2011 07:55
I use HF Classic files to develop and use a procedure to connect to a SQL database. Usually i use the Hyperfile CS which you can connect wih ODBC for reporting (or BI). You can also generate a SQL script from your analyse to create other SQL's but you can't add files and/or fields like you could with Hyperfile (Classic and CS).

Regards, John


von John Gererth - am 03.06.2011 07:57
Hello John

We have to build a set of data from our HF Classic data files and then place the data into SQL tables so it can be accessed by the external BI process. I am therefore building an analysis with my HF classic files and have used them to create an SQL script and have created tables in SQL server. Now I am trying to import the SQL server tables back into the analysis as I think that I should then have no problems swapping the data between the two sets of files.

It is at the point of creating a connection to the SQL server that I am having problems

Regards
Al

von Al - am 03.06.2011 08:40
Hi Al
I don't use Native Access but I've always found I need to add "Integrated Security=SSPI" in the connection string or I get the "can't connect" error. In the analysis I enter it in the datasource as "servername; Integrated Security =SSPI". Works every tiime with OLEDB so maybe it will work for you.

HTH

David

von David Egan - am 03.06.2011 08:41
If the structure of the files is the same you don't need to redefines them in the analysis just use HChangeConnection and hcreation to create the files.
you have to make scripts to create the links defined in the analysis.

to connect to the db just try this:
IF NOT HDescribeConnection("MYCONN","","","ANSERVER\SQLEXPRESS","RptDB"
,hNativeAccessSQLServer,hOReadWrite,"WD Connection Timeout = 600; WD Command Timeout=600") THEN
EndProgram("Error in hdescribeconnexion for SQL - "+CR+ErrorInfo())
END
IF NOT HOpenConnection("MYCONN") THEN
EndProgram("Error in HOpenConnection - "+CR+ErrorInfo())
END
IF NOT HChangeConnection("*","MYCONN") THEN
EndProgram("Error in HChangeConnection - "+CR+ErrorInfo())
END

If the connection problem remains switch from windows authentication to sql server authentication and use some sql server user (for instance sa) to test.

Check in the sql server configuration if the tcp/ip is enable.

von Paulo Oliveira - am 03.06.2011 08:55
Hello David and Paulo

Paulo:
I tried the code you sent and everything worked fine

David
I tried your parameters in Paulo's code using the Native Access but it locked up
I then tried using the OLEDB connector and it worked fine

Using the same parameters in the Analysis connection method did not work.

Thanks to both of you I have a solution and can continue on towards my very optimistic deadlines.
I cannot understand why I am totally unable to create a connection from the analysis and will play around with that when I get time.

Thanks again for your fast responses. :cheers:

Regards
Al



von Al - am 03.06.2011 10:31
Hello

I managed to get a connection running from the analysis to the sql server using the sa user - after enabling it ! Thanks for the tip Paulo


Regards
Al


von Al - am 03.06.2011 11:29
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.