Originally posted by: SystemAdmin

Hello
Just a quick question,
Is it possible to connect to an SQL server 2000 from TX version 6.7 .
I ask because in the adapter list in the DB interface designer only
SQL 6.5 and 7 are listed and our server guys want to upgrade.

Thanks
Alex
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Hi Alex

Yes I'm quite sure you can -- I used both 6.7 and 7.5 with SQL Server 2000 at a previous client. You shouldn't experience any issues but as always thorough testing is advised.

regards

Tarun
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Hi Tarun
OK. Thank you. I assume that would be using the ODBC type then and
not the specific MS 6.5 or 7 type ?

Alex
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Alex,

unfortunately I don't remember and don't have my laptop here with me to check. However I am fairly certain that we used the SQL Server 7 adapter. You could use ODBC if you wanted to, but I wouldn't do it by choice.

The client's system involved TX making large numbers of calls to stored procedures within SQL server 2000.

By the way within the documentation it mentions that you can use the MS SQL Server 7 adapter for database version 7.0 or greater (help file 1066.pdf) . Out of interest, the documentation also mentions:
"If you select MS SQL Server 7 as the Adapter > Type setting, the OLE DB
adapter is used to access SQL Server. OLE DB is the preferred adapter to use for Microsoft SQL Server versions 7.0 and 2000."

:-)

Regards,

Tarun
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

You can use the MS SQL 7 adapter, but if you are using the multiple instance feature of MS SQL 2000, then you will have to create a backward compatible SQL 7 style alias.

You need to do this using MS config tools to 'fool' the adapter into thinking it is still connecting to SQL 7.

This will have to be done for each instance to which you wish to connect.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Thank you for your replies.
I will give it a try.

Have a nice day.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

This is strange.
I used to connect to the SQL 7 server using the ODBC adapter.
I changed the DSN to point to the new SQL 2000 server.
I figured I did not have to change anything in the DB designer since
it just uses the DSN. From the DB designer I tried to generate a typetree
from a query and that worked. I assume it connects to the DB in order to do that. I can even run the map in the command server(in the map designer), but when I run the map with the eventserver I just can not get it to connect.
I also tried using the MS SQL 7 adapter as suggested, but with the same result.
I don't have any additional DB commands specified in my .msd file so it
just "inherits" what is in the map.
Any ideas ?

Thank you and sorry to bug you again.
Alex
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Is the event server running under a user that has permission to the database? I had a similar problem connecting to a foxpro database through odbc - ok through database designer and Command server but wouldn't work through event server on win2000 but it worked on win NT.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

The eventserver runs on a Win2000. It is run by the LocalSystem account but in the DSN setup I told it to verify the authenticity of the user using a login and password entered by the user.
The SQL server runs on 2003.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

I think that's your problem. LocalSystem Account probably doesn't have authentication permission to the server where SQL server is running. Ensure that you run the Event Server under a user that can access the server - try mapping the server drive with the user to see if it requires authentication.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Yes that was exactly what the problem was!
Now it works with the SQL 7 adapter.
Thank you very much for your help.

Have a nice day.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender