Monday, March 26, 2012

opening a db on network path?[sql servr 05]

hi ,
ok i thought it was as simple as putting the path to the db into
AttahDbFileName , but it does'nt work.
I get " is on a network path and this is not supported"
I tried messing with the surface area cfg , no change. Could someone
give me a pointer as to what i have to do to add a database that is on
a network'
Also , The "server" computer that does have the database has SQLSERVER
05 exp. installed. But "client" computer that tries to connect to this
"server" also has SQLSERVER 05 exp. installed , so maybe its using the
wrong instance of the service?
My connection string looks like this:
"Data Source=.\SQLEXPRESS;AttachDbFilename=\\Gidlaptop\data (e)\Gid\My
Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
\DataServices\HTLDATA.mdf;Integrated Security=True;User
Instance=True";
[using C# under .NET 2.0]
Thanks so much!
Gideontry to change the trace flag 1807:
dbcc traceon (1807,-1)
then attach the database using the sp_attach procedure.
I think the user instance option will not be supported in this scenario. you
have to attach the database into SQL Server and keep the database in SQL
Server.
also a user instance as a great impact on the performance.
when there is no connection, SQL Server detach the database. when a new
connection is created, SQL Server attach the database again... this is a big
overhead.
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182001893.244273.221370@.j4g2000prf.googlegroups.com...
> hi ,
> ok i thought it was as simple as putting the path to the db into
> AttahDbFileName , but it does'nt work.
> I get " is on a network path and this is not supported"
> I tried messing with the surface area cfg , no change. Could someone
> give me a pointer as to what i have to do to add a database that is on
> a network'
> Also , The "server" computer that does have the database has SQLSERVER
> 05 exp. installed. But "client" computer that tries to connect to this
> "server" also has SQLSERVER 05 exp. installed , so maybe its using the
> wrong instance of the service?
> My connection string looks like this:
> "Data Source=.\SQLEXPRESS;AttachDbFilename=\\Gidlaptop\data (e)\Gid\My
> Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
> \DataServices\HTLDATA.mdf;Integrated Security=True;User
> Instance=True";
> [using C# under .NET 2.0]
> Thanks so much!
> Gideon
>|||Hi
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182001893.244273.221370@.j4g2000prf.googlegroups.com...
> hi ,
> ok i thought it was as simple as putting the path to the db into
> AttahDbFileName , but it does'nt work.
> I get " is on a network path and this is not supported"
> I tried messing with the surface area cfg , no change. Could someone
> give me a pointer as to what i have to do to add a database that is on
> a network'
> Also , The "server" computer that does have the database has SQLSERVER
> 05 exp. installed. But "client" computer that tries to connect to this
> "server" also has SQLSERVER 05 exp. installed , so maybe its using the
> wrong instance of the service?
> My connection string looks like this:
> "Data Source=.\SQLEXPRESS;AttachDbFilename=\\Gidlaptop\data (e)\Gid\My
> Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
> \DataServices\HTLDATA.mdf;Integrated Security=True;User
> Instance=True";
> [using C# under .NET 2.0]
> Thanks so much!
> Gideon
See http://support.microsoft.com/kb/304261 for Network Attached Storage
John|||hi ,
thanks so much for replying. I'm almost finished making a really huge
app and this is the last step , I really thought this was simple so i
did'nt bother to look into this earlier! so i think i'm in a little
trouble!
I did a little searching, and saw the KB
But I'm really sorry i dont understand this , How and where do i run
those functions , a stored procedure? , i tried searching google , i
dont see sp_attach_db in the T-SQL reference. Could someone tell me
what i have to read to know how to run the aforementioned procedures?
Gideon|||read this:
http://www.databasejournal.com/features/mssql/article.php/2224361
but, why do you want to access a database stored in an UNC path?
its a recommended setup for performance reason.
also there is a risk with this setup in case of network failure.
I recommend to copy the database locally on the server where SQL express is.
or use a remote SQL server (SQL Server is installed on the server A and the
application on the server B)
in the case of a remote server the user instance option doesn't works.
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182016131.904940.224060@.x35g2000prf.googlegroups.com...
> hi ,
> thanks so much for replying. I'm almost finished making a really huge
> app and this is the last step , I really thought this was simple so i
> did'nt bother to look into this earlier! so i think i'm in a little
> trouble!
> I did a little searching, and saw the KB
> But I'm really sorry i dont understand this , How and where do i run
> those functions , a stored procedure? , i tried searching google , i
> dont see sp_attach_db in the T-SQL reference. Could someone tell me
> what i have to read to know how to run the aforementioned procedures?
> Gideon
>|||hi,
thanks again for replying.
I need for the database to be accessed via a UNC path because one of
my software requirements are that it should work over a network.(Its a
hotel manager). This seems like something essential to me! A DB should
be accssible over a network!? Are'nt databases generally used over a
network!?
I initially started working with an accss DB. Then , i was told to
move to sql , and that i would have no worries for the networking
since slq server has to only be installed on the machine that contains
it! =S
>I recommend to copy the database locally on the server where SQL express is.
>or use a remote SQL server (SQL Server is installed on the server A and the
>application on the server B)
>in the case of a remote server the user instance option doesn't works.
I cant afford to make copies, since the app will run at any time on
any pc and it mantains a cache in memory.When one client changes
data , i've even written classes that notify the app on the "server"
pc that broadcasts a "Reload" message.
By remote server , do you mean i need to have sql server on the
"server" pc(the one that contains the db) *and* the "client" pc'(the
one that accesses the db on the server) I did have sql server on BOTH
the pcs when i got the exception i mentioned! How do i call the
correct sql service? And which one do i call'
thanks so much
Gideon|||Hi Gideon
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182023183.774703.115170@.x35g2000prf.googlegroups.com...
> hi,
> thanks again for replying.
> I need for the database to be accessed via a UNC path because one of
> my software requirements are that it should work over a network.(Its a
> hotel manager). This seems like something essential to me! A DB should
> be accssible over a network!? Are'nt databases generally used over a
> network!?
> I initially started working with an accss DB. Then , i was told to
> move to sql , and that i would have no worries for the networking
> since slq server has to only be installed on the machine that contains
> it! =S
>>I recommend to copy the database locally on the server where SQL express
>>is.
>>or use a remote SQL server (SQL Server is installed on the server A and
>>the
>>application on the server B)
>>in the case of a remote server the user instance option doesn't works.
> I cant afford to make copies, since the app will run at any time on
> any pc and it mantains a cache in memory.When one client changes
> data , i've even written classes that notify the app on the "server"
> pc that broadcasts a "Reload" message.
> By remote server , do you mean i need to have sql server on the
> "server" pc(the one that contains the db) *and* the "client" pc'(the
> one that accesses the db on the server) I did have sql server on BOTH
> the pcs when i got the exception i mentioned! How do i call the
> correct sql service? And which one do i call'
> thanks so much
> Gideon
>
In a traditional client/server system the database is on a server and the
data files are local to that server, there is no need to put the database on
a shared drive or open it with the other client (in fact once opened some
can not open it). The clients access the server "over the network". By
default SQL Express will not have network connections enabled, this could
have been activated at installation, but it can still be enabled post
install using SQLSac.exe see my previous post http://tinyurl.com/23dum6 for
more. If you want to connect to that instance change your connection string
to one of those described in "SQL Native Client OLE DB Provider" at
http://www.connectionstrings.com/?carrier=sqlserver2005.
sp_attach_db is documented in Books online all you need to do is type it
into the index, you can also use CREATE DATABASE with the FOR ATTACH clause
(also in Books Online). As you have Express Books online can be downloaded
from http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx or found at
http://msdn2.microsoft.com/en-us/library/ms130214(SQL.90).aspx
HTH
John|||> Are'nt databases generally used over a network!?
Yes, but that's for the database clients. The database server itself should
access the database locally rather than over a network.
This whole thing of not using a network attached drive for a SQL Server
database is not a SQL Server issue. It is a netework protocol issue. The
protocol for attaching to a network drive is CIFS, which was designed for
file sharing, not block I/O traffic. It is a rather chatty protocol, thus not
good for performance. There is also potential issue with the CIFS client
caching data on the client side, which amy cause data integrity problem for
SQL Server.
What does this mean? Basically, you should forget about using a network drive.
Linchi
"giddy" wrote:
> hi,
> thanks again for replying.
> I need for the database to be accessed via a UNC path because one of
> my software requirements are that it should work over a network.(Its a
> hotel manager). This seems like something essential to me! A DB should
> be accssible over a network!? Are'nt databases generally used over a
> network!?
> I initially started working with an accss DB. Then , i was told to
> move to sql , and that i would have no worries for the networking
> since slq server has to only be installed on the machine that contains
> it! =S
> >I recommend to copy the database locally on the server where SQL express is.
> >or use a remote SQL server (SQL Server is installed on the server A and the
> >application on the server B)
> >in the case of a remote server the user instance option doesn't works.
> I cant afford to make copies, since the app will run at any time on
> any pc and it mantains a cache in memory.When one client changes
> data , i've even written classes that notify the app on the "server"
> pc that broadcasts a "Reload" message.
> By remote server , do you mean i need to have sql server on the
> "server" pc(the one that contains the db) *and* the "client" pc'(the
> one that accesses the db on the server) I did have sql server on BOTH
> the pcs when i got the exception i mentioned! How do i call the
> correct sql service? And which one do i call'
> thanks so much
> Gideon
>|||Hi John ,
Thanks for the links , seems Tcp;/IP was disabled here.
HI , Linchi Shea
thanks for replying,
> Yes, but that's for the database clients. The database server itself should
> access the database locally rather than over a network.
The application is a client!
My app doubles as a server or client depending on where the database
is. If the database exists in the same path as the .exe , then it
accesses it from there. The user can install the app on another
computer by selecting a network share. My netinstaller.exe copies the
app there. And in its settings file , sets it to "client". If the app
starts and figures its a "client" , it connects to the database on the
server computer which is on a network share?!
According to you , database clients should access the Db from the
server , which is on net share? That _is_ what i'm doing!!
Thanks so much
Gideon|||Hi Gideon
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182064312.950958.288870@.i13g2000prf.googlegroups.com...
> Hi John ,
> Thanks for the links , seems Tcp;/IP was disabled here.
> HI , Linchi Shea
> thanks for replying,
>> Yes, but that's for the database clients. The database server itself
>> should
>> access the database locally rather than over a network.
> The application is a client!
> My app doubles as a server or client depending on where the database
> is. If the database exists in the same path as the .exe , then it
> accesses it from there. The user can install the app on another
> computer by selecting a network share. My netinstaller.exe copies the
> app there. And in its settings file , sets it to "client". If the app
> starts and figures its a "client" , it connects to the database on the
> server computer which is on a network share?!
> According to you , database clients should access the Db from the
> server , which is on net share? That _is_ what i'm doing!!
> Thanks so much
> Gideon
That is not what we said!
When the machine is a server it should be running SQL Server (Express) with
the network protocols enabled. The database files should reside on this
machine and be attached as local files.
When the machine is a client it should connect to the server machine (using
TCP/IP) and use the database on the server. If you want to create a user
instance for that user, then the files will be still on the server machine
not on the client.
If the client is also the server then it should behave in the same way as
any other client although it would use shared memory to communicate with the
server rather than TCP/IP.
Assumming that you have attached the database with sp_attach_db and now that
you have enabled TCP/IP protocol, you should be able to connect to it from a
different machine to the server using SQLCMD e.g
SQLCMD -S ServerName\SQLExpress -E -d MyDatabase
You should also be able to connect from the server itself by using the same
command or using
SQLCMD -S .\SQLExpress -E -d MyDatabase
If you changed data on one machine those changes will be reflected on the
other if they have been committed.
Hopefully that is now clearer!
John|||and to access your SQL Server server from your client application you have
to use this connection string (instead of the first one you send to us in
the newsgroup):
Data source=<Server Name where SQL Server is>;Initial Catalog=<Name of the
database>;UID=<Username>;pwd=<password>
or
Data source=<Server Name where SQL Server is>;Initial Catalog=<Name of the
database>;integrated security=SSPI
as you can see we don't reference any database file, we reference the server
and the name of the database on this server.
as an application, you don't care where the database files are stored, its
the role of the SQL Server server, not the client application role.
physically, the database files are stored locally on the SQL Server server.
Maybe you have an MS Access background.
MS Access is NOT a server, its just a file format, so you have to reference
the file and the database engine is executed by the client; there is no
database server in this case.
SQL Server IS a database server, which mean that all the commands are send
through the network and executed by the server himself, the client don't
contains any database engine. its why we reference a server + databasename
and not a file in the connection string.
we hope we'll found the right way to help you! :-)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:edYR9bLsHHA.1864@.TK2MSFTNGP04.phx.gbl...
> Hi Gideon
> "giddy" <gidisrael@.gmail.com> wrote in message
> news:1182064312.950958.288870@.i13g2000prf.googlegroups.com...
>> Hi John ,
>> Thanks for the links , seems Tcp;/IP was disabled here.
>> HI , Linchi Shea
>> thanks for replying,
>> Yes, but that's for the database clients. The database server itself
>> should
>> access the database locally rather than over a network.
>> The application is a client!
>> My app doubles as a server or client depending on where the database
>> is. If the database exists in the same path as the .exe , then it
>> accesses it from there. The user can install the app on another
>> computer by selecting a network share. My netinstaller.exe copies the
>> app there. And in its settings file , sets it to "client". If the app
>> starts and figures its a "client" , it connects to the database on the
>> server computer which is on a network share?!
>> According to you , database clients should access the Db from the
>> server , which is on net share? That _is_ what i'm doing!!
>> Thanks so much
>> Gideon
> That is not what we said!
> When the machine is a server it should be running SQL Server (Express)
> with the network protocols enabled. The database files should reside on
> this machine and be attached as local files.
> When the machine is a client it should connect to the server machine
> (using TCP/IP) and use the database on the server. If you want to create a
> user instance for that user, then the files will be still on the server
> machine not on the client.
> If the client is also the server then it should behave in the same way as
> any other client although it would use shared memory to communicate with
> the server rather than TCP/IP.
> Assumming that you have attached the database with sp_attach_db and now
> that you have enabled TCP/IP protocol, you should be able to connect to it
> from a different machine to the server using SQLCMD e.g
> SQLCMD -S ServerName\SQLExpress -E -d MyDatabase
> You should also be able to connect from the server itself by using the
> same command or using
> SQLCMD -S .\SQLExpress -E -d MyDatabase
> If you changed data on one machine those changes will be reflected on the
> other if they have been committed.
> Hopefully that is now clearer!
> John
>|||hi ,
i''m sorry to nag you again ,
> That is not what we said!
ok,
> When the machine is a server it should be running SQL Server (Express) with
> the network protocols enabled. The database files should reside on this
> machine and be attached as local files.
Yea , ok , this is how i do that:
static string connStr = @."Data Source=.\SQLEXPRESS;AttachDbFilename="
+ asmPath+@."\DataServices\HTLDATA.mdf;" +@."Integrated Security=True;";
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query,
connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
> When the machine is a client it should connect to the server machine (using
> TCP/IP) and use the database on the server. If you want to create a user
> instance for that user, then the files will be still on the server machine
> not on the client.
OK , i dont get what you mean by using TCP/IP!? , this is what i did:
(with simple file sharing off)
static string connStr = @."Data Source=xxx.xxx.xxx.xxx
\SQLEXPRESS;AttachDbFilename=" + servrPath+@."\DataServices
\HTLDATA.mdf;" +@."Integrated Security=True;";
> If the client is also the server then it should behave in the same way as
> any other client although it would use shared memory to communicate with the
> server rather than TCP/IP.
By "client is also the server" do you mean if a client machine has sql
server installed?
> Assumming that you have attached the database with sp_attach_db and now that
> you have enabled TCP/IP protocol, you should be able to connect to it from a
> different machine to the server using SQLCMD e.g
Like i said , thats how i use the database , i have no idea about
sp_attach_db? (i'm using MS-SQL Server 2005)
> SQLCMD -S ServerName\SQLExpress -E -d MyDatabase
How do i transfer the connection to my C# code from here?
Thanks so much
Gideon|||DBSERVER is the database SQL Server.
on this server, execute the attach command:
http://msdn2.microsoft.com/en-us/library/ms165673(SQL.90).aspx
in your case the command are:
sqlcmd DBSERVER\SQLExpress
then...
use master
go
create database HTF on
(filename= c:\Gid\My
Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
\DataServices\HTLDATA.mdf),
(filename= c:\Gid\My
Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
\DataServices\HTLDATA.ldf)
for attach;
go
now your database is attached into SQL Server on your DBSERVER
(the files (MDF and LDF) MUST be on the local drive of the DBSERVER)
follows the commands describe in the next document to enable the remote
access to your DBSERVER\SQLExpress instance:
http://msdn2.microsoft.com/en-us/library/ms173748(SQL.90).aspx
you DBSERVER is ready to receive commands from your client application which
is in another server or desktop.
in your client application code the commands will be:
static string connStr = @."Data
Source=DBSERVER\SQLEXPRESS;AttachDbFilename=Initial Catalog=HTL;Integrated
Security=True;";
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query,
connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
hope this will works for you.
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182090128.264954.31120@.o11g2000prd.googlegroups.com...
> hi ,
> i''m sorry to nag you again ,
>
>> That is not what we said!
> ok,
>> When the machine is a server it should be running SQL Server (Express)
>> with
>> the network protocols enabled. The database files should reside on this
>> machine and be attached as local files.
> Yea , ok , this is how i do that:
> static string connStr = @."Data Source=.\SQLEXPRESS;AttachDbFilename="
> + asmPath+@."\DataServices\HTLDATA.mdf;" +@."Integrated Security=True;";
> using (SqlConnection connection = new SqlConnection(connStr))
> {
> SqlDataAdapter adapter = new SqlDataAdapter();
> adapter.SelectCommand = new SqlCommand(query,
> connection);
> DataSet dataset = new DataSet();
> adapter.Fill(dataset);
> return dataset.Tables[0];
> }
>
>> When the machine is a client it should connect to the server machine
>> (using
>> TCP/IP) and use the database on the server. If you want to create a user
>> instance for that user, then the files will be still on the server
>> machine
>> not on the client.
> OK , i dont get what you mean by using TCP/IP!? , this is what i did:
> (with simple file sharing off)
> static string connStr = @."Data Source=xxx.xxx.xxx.xxx
> \SQLEXPRESS;AttachDbFilename=" + servrPath+@."\DataServices
> \HTLDATA.mdf;" +@."Integrated Security=True;";
>> If the client is also the server then it should behave in the same way as
>> any other client although it would use shared memory to communicate with
>> the
>> server rather than TCP/IP.
> By "client is also the server" do you mean if a client machine has sql
> server installed?
>> Assumming that you have attached the database with sp_attach_db and now
>> that
>> you have enabled TCP/IP protocol, you should be able to connect to it
>> from a
>> different machine to the server using SQLCMD e.g
> Like i said , thats how i use the database , i have no idea about
> sp_attach_db? (i'm using MS-SQL Server 2005)
>> SQLCMD -S ServerName\SQLExpress -E -d MyDatabase
> How do i transfer the connection to my C# code from here?
>
> Thanks so much
> Gideon
>|||It has nothing to do with your app being run as server app or client app.
1. It is the SQL Server/Express' requirement that its data file
(*.mdf/*.ldf) MUST be on a local drive of the computer where SQL
Server/Express is installed. That is how SQL Server/Express designed, no
matter what you do, you have to meet the requirement. Period.
2. Your app, whether it is server app or client app, does not access *.mdf
file to access data. It access SQL Server/Express for the data. No
application can use *.mdf file but SQL Server/Express.
3. When you "install" your app to a network drive (I do not know how you do
it), it does not necessarily make it "server" application. Say, you place
your .NET app's *.exe on a network drive to your computer, and run it on
your computer, this is not much different froom you run it from your own
computer, except the code is loaded from the network (you need configure
.NET code security in this case), the app is still sits in memory of your
computer and run locally.
4. Make sure your app really needs to use SQ: Server Express' "USER
INSTANCE". You only need to have "AttachDBFile=..." in connectionString
when you use "User Instance". Otherwise, there is no need "attach" a
database to SQL Server everytime your app connects to SQLServer/Express. I
am sure if you really mean to use "User Instance", then you know it only
allows single user access to the database (the current Windows log on user
who runs the application).
"giddy" <gidisrael@.gmail.com> wrote in message
news:1182001893.244273.221370@.j4g2000prf.googlegroups.com...
> hi ,
> ok i thought it was as simple as putting the path to the db into
> AttahDbFileName , but it does'nt work.
> I get " is on a network path and this is not supported"
> I tried messing with the surface area cfg , no change. Could someone
> give me a pointer as to what i have to do to add a database that is on
> a network'
> Also , The "server" computer that does have the database has SQLSERVER
> 05 exp. installed. But "client" computer that tries to connect to this
> "server" also has SQLSERVER 05 exp. installed , so maybe its using the
> wrong instance of the service?
> My connection string looks like this:
> "Data Source=.\SQLEXPRESS;AttachDbFilename=\\Gidlaptop\data (e)\Gid\My
> Creations\Hotel Management Studio\HotelManager.Core\bin\Debug
> \DataServices\HTLDATA.mdf;Integrated Security=True;User
> Instance=True";
> [using C# under .NET 2.0]
> Thanks so much!
> Gideon
>|||hi Jeje, i did'nt see your reply. I only saw John Bells post before
sending my earlier post.
Thank you oh so much. It works perfectly! I used the second connection
string you mentioned , the attaching commands worked perfectly too!
Also, Thank you Norman for clearing things up. Heres where my mis
understading was:
>2. Your app, whether it is server app or client app, does not access *.mdf
>file to access data. It access SQL Server/Express for the data. No
>application can use *.mdf file but SQL Server/Express.
I just kept thinking everyone said i should be working over a network
and that it was impossible, but it was more like i should'nt access a
file over a network , but call the server.
Just one more things and then i'm done nagging you guys!
>. When you "install" your app to a network drive (I do not know how you do
>it), it does not necessarily make it "server" application. Say, you place
>your .NET app's *.exe on a network drive to your computer, and ****run it on
>your computer, this is not much different froom you run it from your own
>computer, except the code is loaded from the network ******(you need configure
>.NET code security in this case), the app is still sits in memory of your
>computer and run locally.
(Slightly off topic! =(
Acutally , when the settings file tells the app its a server or a
client , many parts of the program run differently. I really would'nt
want someone running a "server app" instance from a "client" computer.
What security do i have to configure?
Thanks a million!
Gideon

No comments:

Post a Comment