By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Problem with Stored Procedures and shared user name

P: n/a
Hi all,
I'm convertinmg an Access 2002 application to SQL Server with many
differant locations, each needs their own instance of the database on
the same physical server so I have created an INI file to provide the
server name (i.e. SQL1) and DSN (i.e UK Marketing)
It works fine for linking the tables BUT when I run the sprocs it will
only work if the generic and shared user name I use has its default
database set to the datbase I want to execute against. The connection
string is

strConnect = "Server=SQL1;DSN=UK Marketing;DRIVER=SQL
Server;UID=User;PWD=xyz"

This won't then allow me to change the DSN to say 'France Marketing'
and keep the user the same. Do I need users for each market? Bit of a
pain if I do as I only need it to access the sprocs on each of the
database on the server. I did try the InitialCatalog options but that
did not help either

Ideas?

Cheers
shaun
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your connection string for a DSN-less ODBC connection is incorrect. It
should be like this.

strConnect = "ODBC;Driver=SQL Server;Server=SQL1;" & _
"Database=UK Marketing;UID=user;PWD=xyz"

Unless you are actually trying to connect thru a DSN (your original
string was ambiguous). If the connection is truly thru a DSN, then the
connection string is like this:

strConnect = "DSN=UK Marketing;UID=user;PWD=xyz"

The InitialCatalog parameter is part of an ADO connection string. The
above connection string examples are for DAO. Which are you using?

To access each database your User must be granted access to each DB. To
run an SP on each DB, the user has to have EXEC rights granted on each
SP in each DB. Do you have one DB with different schemas (owners: e.g.:
UK Marketing, France Marketing, etc.) or many DBs with one owner each?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm6hmYechKqOuFEgEQLjBgCg6jjjkvQWCw22cm/r3V4AjCg9V/8AnjzJ
NKvZqrtWGB/rqDPNqQ648UhG
=+D8U
-----END PGP SIGNATURE-----
Shaun wrote:
Hi all,
I'm convertinmg an Access 2002 application to SQL Server with many
differant locations, each needs their own instance of the database on
the same physical server so I have created an INI file to provide the
server name (i.e. SQL1) and DSN (i.e UK Marketing)
It works fine for linking the tables BUT when I run the sprocs it will
only work if the generic and shared user name I use has its default
database set to the datbase I want to execute against. The connection
string is

strConnect = "Server=SQL1;DSN=UK Marketing;DRIVER=SQL
Server;UID=User;PWD=xyz"

This won't then allow me to change the DSN to say 'France Marketing'
and keep the user the same. Do I need users for each market? Bit of a
pain if I do as I only need it to access the sprocs on each of the
database on the server. I did try the InitialCatalog options but that
did not help either

Nov 13 '05 #2

P: n/a
Cheers !!
I was being an arse and copying DSN connection string where I really
needed the database parameter
All fixed, thansk for that
Shaun
sh********@msn.com (Shaun) wrote in message news:<4a**************************@posting.google. com>...
Hi all,
I'm convertinmg an Access 2002 application to SQL Server with many
differant locations, each needs their own instance of the database on
the same physical server so I have created an INI file to provide the
server name (i.e. SQL1) and DSN (i.e UK Marketing)
It works fine for linking the tables BUT when I run the sprocs it will
only work if the generic and shared user name I use has its default
database set to the datbase I want to execute against. The connection
string is

strConnect = "Server=SQL1;DSN=UK Marketing;DRIVER=SQL
Server;UID=User;PWD=xyz"

This won't then allow me to change the DSN to say 'France Marketing'
and keep the user the same. Do I need users for each market? Bit of a
pain if I do as I only need it to access the sprocs on each of the
database on the server. I did try the InitialCatalog options but that
did not help either

Ideas?

Cheers
shaun

Nov 13 '05 #3

P: n/a
Fixed :)
I needed the Database= option
Serves me right for coyping a DSN connection string :-(
Cheers
Shaun
sh********@msn.com (Shaun) wrote in message news:<4a**************************@posting.google. com>...
Hi all,
I'm convertinmg an Access 2002 application to SQL Server with many
differant locations, each needs their own instance of the database on
the same physical server so I have created an INI file to provide the
server name (i.e. SQL1) and DSN (i.e UK Marketing)
It works fine for linking the tables BUT when I run the sprocs it will
only work if the generic and shared user name I use has its default
database set to the datbase I want to execute against. The connection
string is

strConnect = "Server=SQL1;DSN=UK Marketing;DRIVER=SQL
Server;UID=User;PWD=xyz"

This won't then allow me to change the DSN to say 'France Marketing'
and keep the user the same. Do I need users for each market? Bit of a
pain if I do as I only need it to access the sprocs on each of the
database on the server. I did try the InitialCatalog options but that
did not help either

Ideas?

Cheers
shaun

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.