Database Connection

[UMehmood]

I am developing Infopath forms for my company, which are
supposed to be linked to database at the backend (SQL or
Access) and are published on a sharepoint server.

It seems to work fine on the local machine when i query
and update the forms, but when I open the forms from the
remote machine (which is how it is actually going to be
used) it asks for the login and password for the SQL
server trusted connection or the path for the MDB file
incase of Access from the LOCAL MACHINE!?

The ODBC connections and mdb files are ofcourse on the
server, I am probably missing some setting or something
like that..

Can anyone help me with that?

 

[UMehmood]

No... the database is local to the server!

Consider the scenario in which the infopath form and the database reside on
the server... i design n test the form on server and it works fine... now
when I want to publish it and give access to other users so that they can
open the form from there own machines obviously the database would not be on
his local machines... Infopath searches the database on the users machine
rather than updating database on the server...

There must be some setting to that since if it can ony connect to local
databases and ODBCs... it kind of defeats the whole purpose... so can you
help me now?

Thanks for the earlier response aswell

Regards,
UMehmood

 

[UMehmood]

its a bit like scenario 1... but ill give you the details yet again... n I am
very thankful for your earlier responses and hoping to get a swift one this
time aswell!

-I have a server which hosts SQL Server and Sharepoint.. beside office and
Infopath.. lets just call that Server A
-I design my infopath forms on Server A... connected to a system DSN named
"Test" of SQL Server
-after testing and everything.. i publish it on the sharepoint (same server A)
-i can open that from the sharepoint site and fill in the values and use it
from the server (Server A)
-When i access the sharepoint from my laptop... the form searches for DSN
Named "Test" on my laptop (which obviously is not there!)... n does not
connect to the Servers ODBC connection... same is the case with MS Access
database.. it works fine on the server but from laptop.. it starts to seacrh
for the MDB file on the local machine rather than connecting to MDB file on
the server

Shouldn't the form save the network path itself and connect to the server
database when it is being opened remotely? If Yes.. how?

in the manifest.xsf... it has the following line:
<xsf:adoAdapter connectionString="Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Test;Data
Source=ServerA;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=PS-LAPTOP;Use Encryption for Data=False;Tag with
column collation when possible=False" commandText="select ... (i cut off the
query);" queryAllowed="yes" name="Main connection"
submitAllowed="yes"></xsf:adoAdapter>

I hope I have been able to clarify at this time!

Please help

Thanks yet again

Regards,
UMehmood

I'm still trying to fully understand. Please review the following 3 scenarios and let me know if I am close on any of them.

Scenario 1:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You publish your form to another location on Server A where users have access.
4. Users access the form on Server A in the published location.
5. The database connection no longer works.

Scenario 1 should just work.


Scenario 2:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You publish your form to Server B where users have access.
4. Users access the form on Server B in the published location.
5. The database connection to Server A no longer works.

Scenario 2 may require you to fix your data connection in the manifest.xsf file if it wasn't given a full network path to the database to begin with.


Scenario 3:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You distribute your form as an .msi file that is installed on each users local machine.
4. Users access the form from their local installation.
5. The database connection to Server A no longer works.

Scenario 3 may require you to fix your data connection in the manifest.xsf file if it wasn't given a full network path to the database to begin with.


--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



No... the database is local to the server!

Consider the scenario in which the infopath form and the database reside on
the server... i design n test the form on server and it works fine... now
when I want to publish it and give access to other users so that they can
open the form from there own machines obviously the database would not be on
his local machines... Infopath searches the database on the users machine
rather than updating database on the server...

There must be some setting to that since if it can ony connect to local
databases and ODBCs... it kind of defeats the whole purpose... so can you
help me now?

Thanks for the earlier response aswell

Regards,
UMehmood

It sounds as though when you published to the remote server, you did not also update the data connection to use the remote database instead of the local one. Was this the case?

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



I am developing Infopath forms for my company, which are
supposed to be linked to database at the backend (SQL or
Access) and are published on a sharepoint server.

It seems to work fine on the local machine when i query
and update the forms, but when I open the forms from the
remote machine (which is how it is actually going to be
used) it asks for the login and password for the SQL
server trusted connection or the path for the MDB file
incase of Access from the LOCAL MACHINE!?

The ODBC connections and mdb files are ofcourse on the
server, I am probably missing some setting or something
like that..

Can anyone help me with that?

 

[UMehmood]

oohkay.. i have tried that aswell... It doesn't work either!
anyother tip that I can try?

I haven't verified this yet, so be patient if this doesn't work...

But there may be an issue with the fact that your published server is also your local server... I'm wondering if you would have better results, if you took your final form over to your laptop, and then published it across from the laptop back to Server A.

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



its a bit like scenario 1... but ill give you the details yet again... n I am
very thankful for your earlier responses and hoping to get a swift one this
time aswell!

-I have a server which hosts SQL Server and Sharepoint.. beside office and
Infopath.. lets just call that Server A
-I design my infopath forms on Server A... connected to a system DSN named
"Test" of SQL Server
-after testing and everything.. i publish it on the sharepoint (same server A)
-i can open that from the sharepoint site and fill in the values and use it
from the server (Server A)
-When i access the sharepoint from my laptop... the form searches for DSN
Named "Test" on my laptop (which obviously is not there!)... n does not
connect to the Servers ODBC connection... same is the case with MS Access
database.. it works fine on the server but from laptop.. it starts to seacrh
for the MDB file on the local machine rather than connecting to MDB file on
the server

Shouldn't the form save the network path itself and connect to the server
database when it is being opened remotely? If Yes.. how?

in the manifest.xsf... it has the following line:
<xsf:adoAdapter connectionString="Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Test;Data
Source=ServerA;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=PS-LAPTOP;Use Encryption for Data=False;Tag with
column collation when possible=False" commandText="select ... (i cut off the
query);" queryAllowed="yes" name="Main connection"
submitAllowed="yes"></xsf:adoAdapter>

I hope I have been able to clarify at this time!

Please help

Thanks yet again

Regards,
UMehmood

I'm still trying to fully understand. Please review the following 3 scenarios and let me know if I am close on any of them.

Scenario 1:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You publish your form to another location on Server A where users have access.
4. Users access the form on Server A in the published location.
5. The database connection no longer works.

Scenario 1 should just work.


Scenario 2:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You publish your form to Server B where users have access.
4. Users access the form on Server B in the published location.
5. The database connection to Server A no longer works.

Scenario 2 may require you to fix your data connection in the manifest.xsf file if it wasn't given a full network path to the database to begin with.


Scenario 3:
1. You have Server A which you are using to design and test your form.
2. The database resides on Server A.
3. You distribute your form as an .msi file that is installed on each users local machine.
4. Users access the form from their local installation.
5. The database connection to Server A no longer works.

Scenario 3 may require you to fix your data connection in the manifest.xsf file if it wasn't given a full network path to the database to begin with.


--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



No... the database is local to the server!

Consider the scenario in which the infopath form and the database reside on
the server... i design n test the form on server and it works fine... now
when I want to publish it and give access to other users so that they can
open the form from there own machines obviously the database would not be on
his local machines... Infopath searches the database on the users machine
rather than updating database on the server...

There must be some setting to that since if it can ony connect to local
databases and ODBCs... it kind of defeats the whole purpose... so can you
help me now?

Thanks for the earlier response aswell

Regards,
UMehmood

It sounds as though when you published to the remote server, you did not also update the data connection to use the remote database instead of the local one. Was this the case?

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



I am developing Infopath forms for my company, which are
supposed to be linked to database at the backend (SQL or
Access) and are published on a sharepoint server.

It seems to work fine on the local machine when i query
and update the forms, but when I open the forms from the
remote machine (which is how it is actually going to be
used) it asks for the login and password for the SQL
server trusted connection or the path for the MDB file
incase of Access from the LOCAL MACHINE!?

The ODBC connections and mdb files are ofcourse on the
server, I am probably missing some setting or something
like that..

Can anyone help me with that?

 

[Steve van Dongen [MSFT]]

-I have a server which hosts SQL Server and Sharepoint.. beside office and
Infopath.. lets just call that Server A
-I design my infopath forms on Server A... connected to a system DSN named
"Test" of SQL Server
-after testing and everything.. i publish it on the sharepoint (same server A)
-i can open that from the sharepoint site and fill in the values and use it
from the server (Server A)
-When i access the sharepoint from my laptop... the form searches for DSN
Named "Test" on my laptop (which obviously is not there!)... n does not
connect to the Servers ODBC connection... same is the case with MS Access
database.. it works fine on the server but from laptop.. it starts to seacrh
for the MDB file on the local machine rather than connecting to MDB file on
the server

Shouldn't the form save the network path itself and connect to the server
database when it is being opened remotely? If Yes.. how?

in the manifest.xsf... it has the following line:
<xsf:adoAdapter connectionString="Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Test;Data
Source=ServerA;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=PS-LAPTOP;Use Encryption for Data=False;Tag with
column collation when possible=False" commandText="select ... (i cut off the
query);" queryAllowed="yes" name="Main connection"
submitAllowed="yes"></xsf:adoAdapter>

Edit the connection string(s) in the manifest.xsf files to refer to
the actual database and not the Test system DSN.

Regards,
Steve