Do I need SQL Express

I am trying out a Starter Kit which uses SQL Express. I have SQL 2005 Developer and .Net 2.0 framework installed on my XP machine. Do I need to install SQL Express as well? Can't the application work with the full blown version of SQL?

This is my connection string:

<add name="ClubSiteDB" connectionString="initial catalog=ClubDB;Data Source=.\SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/>

This is the error I get:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Thanks

You can use your installed SQL but you'll need to modify the connection string and initialize the membership tables in it.

Jeff

|||

Thanks for the response Jeff. What would be the Connection string?

|||

NO. you don't need to install sql express.

Make sure your club.mdf has been attached into your sql server 2005 developer version and then modify your connection string like this:

<add name="constrname" connectionString="Data Source=(local);Initial Catalog=club;Integrated Security=True"
providerName="System.Data.SqlClient" />

Hope my suggestion helps

Do I need second license?

Hello,
I have MS SQL Server 2005 Standard Edition with one Client Access License.
May I
install the server as Primary on one machine and as Secondary (backup)
with Log Shipping on it on another machine, using same license?
Thanks,
GBYes,
But, note, its there for backup only and should not be used to run queries
etc...
So, if you ship the logs across to the backup server and then use database
snapshots then you are probably breaking the liecence terms and need another
liecence.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"GB" <gennady@.telus.net> wrote in message
news:79SJf.7479$n67.3175@.edtnps89...
> Hello,
> I have MS SQL Server 2005 Standard Edition with one Client Access License.
> May I
> install the server as Primary on one machine and as Secondary (backup)
> with Log Shipping on it on another machine, using same license?
> Thanks,
> GB
>|||Since GB really asked two questions (one in the subject and one in the body)
in which a YES can answer both but it has opposite meaning I wanted to say:
No you don't need two licenses<g>.
Andrew J. Kelly SQL MVP
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:O0phfIUNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Yes,
> But, note, its there for backup only and should not be used to run queries
> etc...
> So, if you ship the logs across to the backup server and then use database
> snapshots then you are probably breaking the liecence terms and need
> another liecence.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "GB" <gennady@.telus.net> wrote in message
> news:79SJf.7479$n67.3175@.edtnps89...
>|||Lol - teach me for reading the Q and ignoring the subject - doh!
Absolutely Andrew, and thanks for waking me up!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e7XMoyVNGHA.536@.TK2MSFTNGP09.phx.gbl...
> Since GB really asked two questions (one in the subject and one in the
> body) in which a YES can answer both but it has opposite meaning I wanted
> to say: No you don't need two licenses<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:O0phfIUNGHA.2472@.TK2MSFTNGP11.phx.gbl...
>

DNS required ?

Hi All!
I facing the following problem:
Sometimes i get error while connecting to my SQLServer instance from client
machine "Timeout expired". I found it occurs when my DNS server is not reach
eable from client machine. But i connecting through alias, created by Client
network utility, where i s
pecify directly IP-address, NOT DNS-name ! I tried to connect through QA, os
ql - resuls the same.
Why it requires DNS ? What parameter i missing ?
Telnet on IP-address port works normally.
Server - NT 4.0 SP6 PDC, SQLServer 2000 Std Ed
Client - Windows 98 or NT 4.0IF you try to connect to a server named London, the name London needs to be
resolved to the IP address. This is the function that DNS provides...
However if you use theIP address yourself, DNS does not have to do anything.
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Slava Melnikov" <vasya000@.yandex.ru> wrote in message
news:F6CBFA12-2F13-452B-B848-5BA82228D0A1@.microsoft.com...
> Hi All!
> I facing the following problem:
> Sometimes i get error while connecting to my SQLServer instance from
client machine "Timeout expired". I found it occurs when my DNS server is
not reacheable from client machine. But i connecting through alias, created
by Client network utility, where i specify directly IP-address, NOT DNS-name
! I tried to connect through QA, osql - resuls the same.
> Why it requires DNS ? What parameter i missing ?
> Telnet on IP-address port works normally.
> Server - NT 4.0 SP6 PDC, SQLServer 2000 Std Ed
> Client - Windows 98 or NT 4.0|||What version of the SQL Server ODBC driver (sqlsrv32.dll) is installed on
the client. There is a issue with wsome of the earlier MDAC 2.6 SQL Server
ODBC drivers. If your driver version is 2000.8.0.194 you may be running
into this problem. It also occurs on some later versions as well. If you
install one of the more recent versions of MDAC, such as MDAC 2.7 then the
problem I am describing will not occur. Of course this assumes that you are
using the version of MDAC that has the problem.
When you ping the ip address does the response come back quickly or dies it
take some time?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||O-oh!
sqlsrv32.dll version is really 2000.8.0.194.
Thanks for help, Rand !
I should install latest service pack or new MDAC it's
enough ?
ping works normally

>--Original Message--
>What version of the SQL Server ODBC driver
(sqlsrv32.dll) is installed on
>the client. There is a issue with wsome of the earlier
MDAC 2.6 SQL Server
>ODBC drivers. If your driver version is 2000.8.0.194 you
may be running
>into this problem. It also occurs on some later versions
as well. If you
>install one of the more recent versions of MDAC, such as
MDAC 2.7 then the
>problem I am describing will not occur. Of course this
assumes that you are
>using the version of MDAC that has the problem.
>When you ping the ip address does the response come back
quickly or dies it
>take some time?
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

DMZ to SQL on ISA server

The problem I have is that ISA Server cant listen for
requests on port 1433 as SQL Server which is running on
the same machine has binded to port 1433 on both the
internal and external IP address.
what i need is a way to stop SQL Server from binding to
the external IP so i can setup ISA to do so
Hi Fabian,
There's isn't a way to limit the binding for SQL unfortunately. Your
only options are:
1. Put SQL on another machine & use Server Publishing. SQL could be on the
internal network and not in the DMZ as well.
2. Create a packet filter.
Option 1, would be preferable.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

DMZ to SQL on ISA server

The problem I have is that ISA Server cant listen for
requests on port 1433 as SQL Server which is running on
the same machine has binded to port 1433 on both the
internal and external IP address.
what i need is a way to stop SQL Server from binding to
the external IP so i can setup ISA to do soHi Fabian,
There's isn't a way to limit the binding for SQL unfortunately. Your
only options are:
1. Put SQL on another machine & use Server Publishing. SQL could be on the
internal network and not in the DMZ as well.
2. Create a packet filter.
Option 1, would be preferable.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Distruibuted Reports showing old machine name in the url.

I have a machine with Sql Server 2005 running the Reporting Services. The
server runs both the front-end and back-end.
I change the machine name after installation of Sql Server 2005 and the
Reporting Services.
I subscribed to a report, but the report is delivered to me with the old
machine name in the url.
E.g.
http://ewisql4/reportserver?%2fUNAUTHORIZED+JOB+CREATION+MONITORING%2fUNAUTHORIZED+JOB+CREATION+ORS&rs%3aParameterLanguage=en-US
But it should be:
http://ewisql3/reportserver?%2fUNAUTHORIZED+JOB+CREATION+MONITORING%2fUNAUTHORIZED+JOB+CREATION+ORS&rs%3aParameterLanguage=en-US
What can do to resolve this? Thanks in advanceHi,
Try to change the SSRS box name in the %Program Files%\Microsoft SQL
Server\MSSQL.3\Reporting Services\ReportServer\ folder,
rsreportserver.config file.
Change the value of the <UrlRoot>http://ewisql4/reportserver</UrlRoot> to
<UrlRoot>http://ewisql3/reportserver</UrlRoot> or whatever the new server
name.
Restart the SSRS service, then try to render the report again.
I hope it will help for you.
Regards,
Janos
"yardi4life" <yardi4life.nospam@.discussions.microsoft.com> wrote in message
news:6C3C9E0C-0F36-4DDD-BB2F-F960FED4299F@.microsoft.com...
>I have a machine with Sql Server 2005 running the Reporting Services. The
> server runs both the front-end and back-end.
> I change the machine name after installation of Sql Server 2005 and the
> Reporting Services.
> I subscribed to a report, but the report is delivered to me with the old
> machine name in the url.
> E.g.
> http://ewisql4/reportserver?%2fUNAUTHORIZED+JOB+CREATION+MONITORING%2fUNAUTHORIZED+JOB+CREATION+ORS&rs%3aParameterLanguage=en-US
> But it should be:
> http://ewisql3/reportserver?%2fUNAUTHORIZED+JOB+CREATION+MONITORING%2fUNAUTHORIZED+JOB+CREATION+ORS&rs%3aParameterLanguage=en-US
> What can do to resolve this? Thanks in advance
>
>

Distrubuter has sick hardware. Need to change box.

We run our distribution on another server ie not on the publisher.
We have had some hardware errors and to be honest it's an old machine and
spares are getting sparse.
Is there a step by step for moving distribution to another server?
Thanks
Paul
The short answer is no. The longer answer is no there is not step by step
guide for moving a distributor to another server.
An even longer answer is, no, but it is possible to do a tape restore to the
new distributor with the same name bounce it and hope for the best. I don't
like remote distributors. DBAs IMHO are too quick to migrate to them, and
you need to cluster them otherwise you have a single point of failure which
can be problematic for you as you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Cahill" <noname@.anon.com> wrote in message
news:e6FVdryzFHA.2792@.tk2msftngp13.phx.gbl...
> We run our distribution on another server ie not on the publisher.
> We have had some hardware errors and to be honest it's an old machine and
> spares are getting sparse.
> Is there a step by step for moving distribution to another server?
> Thanks
> Paul
>

distributor_admin Issue....But different than normal

I am having the common "distributor_admin does not exist" error when
trying to set up a machine as its own distributor. (First time after
setting up about 200 distributors). I get this whether I'm doing it
remotely, or on the machine itself.
The wrinkle is that the login "distributor_admin" does not exist at all
on the potential publisher (who, again, is going to be its own
distributor).
Do I need to reinstall MS-SQL to get that login created? Or can I do
it by hand somehow?
Or am I barking up the wrong tree?
Thank you!
-Joseph
What happens when you do a select @.@.servername?
Does it match what you get when you issue a xp_cmdshell 'hostname'?
if not you need to do the following
sp_dropserver @.server='oldServername'
sp_addserver @.server='NewServerName', @.local='LOCAL'
Then restart SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joseph" <josephsheppard@.gmail.com> wrote in message
news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
>I am having the common "distributor_admin does not exist" error when
> trying to set up a machine as its own distributor. (First time after
> setting up about 200 distributors). I get this whether I'm doing it
> remotely, or on the machine itself.
> The wrinkle is that the login "distributor_admin" does not exist at all
> on the potential publisher (who, again, is going to be its own
> distributor).
> Do I need to reinstall MS-SQL to get that login created? Or can I do
> it by hand somehow?
> Or am I barking up the wrong tree?
> Thank you!
> -Joseph
>
|||Thanks, Hilary...There certainly was a difference...
I followed your instructions, and I'm going to take another stab at
setting distribution up.
You are the man! I'm going to have my boss order your book...
-Joseph
Hilary Cotter wrote:[vbcol=seagreen]
> What happens when you do a select @.@.servername?
> Does it match what you get when you issue a xp_cmdshell 'hostname'?
> if not you need to do the following
> sp_dropserver @.server='oldServername'
> sp_addserver @.server='NewServerName', @.local='LOCAL'
> Then restart SQL Server.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Joseph" <josephsheppard@.gmail.com> wrote in message
> news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...

Distributor SQLSERVERAgent Account

What account should the SqlServerAgent run under at the distributor
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?

> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>

Distributor server

I have a merge replication environment with 1 publisher/distributor in the
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>

Distribution database gone but now can't get rid of publications

I have been messing around with Replication on MS Virtual Server.
Set up a Distributor on a seperate machine from the publication
database.
For some reason unkown my Distribution database went in Suspect Mode.
Finally got rid of it using
sp_dropdistributiondb @.database = 'distribution'
Now I can't get delete any of my publications.
They still look towards my Distribution database which is no longer a
distributor
tried
exec sp_dropsubscription
@.publication='AdventureWorksCentralPublication',@.a rticle='all',@.subscriber='all',
@.ignore_distributor=1
sp_removedbreplication
SHould I just re-set up my Distributor again and leave the
publications? Would that even work?
THere has to be some way to delete my publications
Thanks
Mike
Did you run sp_removedbreplication on the publisher and subscribers also? If
so, and you have removed the distribution database then perhaps you are just
seing residual data from tempdb which is reported in the replication monitor.
try refreshing the replication monitor (sp_MSload_replication_status) to see
if this removes the reference.
Paul Ibison
|||Did you follow the steps outlined here?
http://support.microsoft.com/kb/324401
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1176965268.535472.269400@.b58g2000hsg.googlegr oups.com...
>I have been messing around with Replication on MS Virtual Server.
> Set up a Distributor on a seperate machine from the publication
> database.
> For some reason unkown my Distribution database went in Suspect Mode.
> Finally got rid of it using
> sp_dropdistributiondb @.database = 'distribution'
> Now I can't get delete any of my publications.
> They still look towards my Distribution database which is no longer a
> distributor
> tried
> exec sp_dropsubscription
> @.publication='AdventureWorksCentralPublication',@.a rticle='all',@.subscriber='all',
> @.ignore_distributor=1
> sp_removedbreplication
> SHould I just re-set up my Distributor again and leave the
> publications? Would that even work?
> THere has to be some way to delete my publications
> Thanks
> Mike
>

distributing subscriber database on multiple machine - install

Hi,

Is it possible to create a 'master' subscriber, back-up the database with the replication triggers and subscription intact, then restore this subscriber database on multiple subscribers during an install? The subscription is for an anonymous web-synced publication, up to 40 subscribers, and I am trying to avoid the initial snapshot download.

Thanks,

Darrell Young
Hi Darrell,

Yes, it is possible to avoid the initial snapshot downlad on your subscribers. An alternative maybe, to initialize snapshot from backup. More information can be found in book online.

Initializing a Merge Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/ee16af24-d7e2-4b65-a25f-dc89caba2ea2.htm

Initializing a Transactional Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Regards,

Gary Chen|||Gary,

Thanks for the reply. The issue is have is that the subscribers will only have a subset of the data and the schema of the published database, so wholly backing up and restoring the published database is not practical. I had tried taking a subscriber database, then removing the subscription and creating a backup from that - the issue is that the rowguids are removed from the tables. I suppose the choice I have left is to create a backup of the published database, restore on a subscriber, manually remove the data and schema I am not including in the publication, then create a backup of that.
Thanks,

Darrell Young
|||

Hi Darrell,

Try the following,

- Create a snapshot at the publisher

- Download the initial snapshot to one subscriber S1

- Back up the subscriber database at S1

- Before you restore the back up database to other subscriber S2, drop the subscription that you have created before at subscriber S2

- Restore the S1 backup to S2 with KEEP_REPLICATION off (I believe by default it is off)

- Re-create the subcription with no-sync option.

You probably want to make sure that no data get updated to your publisher or subscriber database while you perform this backup-restore.

Try it on one or two machines to see if this work before you restore to all 40 machines.

Regards,

Gary Chen

Distributing database with app

Sorry if this something beaten to death already...

What is/should be required for a target client machine to use and connect to a local copy of a SqlExpress database? Can't seem to get a db connection established to a different machine through a VB6 app. Installed the sql native client on the machine, but still can't connect. What am I missing, or do I have to distribute/install the entire SqlExpress client? Working fine from my development machine.

Rick

If you are connecting via TCP/IP you have to enable these protocols. You also have to ensure to enable remote connections.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I'm not using a remote connection. It's a local copy of the database. What simply does the target deployment machine require or in order for the VB app to connect to the db?

|||The deployment machine needs a Data access components like MDAC or SQL Native Client, both the app machine and the database machine must be able to negotiate to a common protocol like TCP/IP. If that is used you have to allow remote connections on the database server and if you are using another port than 1433 either specify that in your connecting string like MachineName\SQLExpress,Portnumber or start SQL Browser on the database server which will automatically redirect the request to the appropiate port.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks Jens. I don't quite understand what you're saying with remote connections. There is no database machine. This is all starting to sound like every target machine for my application requires SqlExpress be installed on that machine, or a server that machine is connected to. Is this correct or not?

|||

Do you have SQL Express installed anywhere?

You must have a SQL Server running in order to access your data. There are two basic models to do this:

Local Data Access - SQL Express is installed on the same computer with the application. Each user has thier own copy of the database installed on their computer and data is not shared. This is the configuration for a single user application.|||Mike ... I was speaking of Local access (and think I used the term "local" a few times). All I've been asking is -- must SqlExpress be installed a client machine, be it local or server? Apparently, the answer is yes, which complicates distribution and installation by end-users who won't have a clue. I was under the mistaken impression it was not unlike distributing an Access mdb file. I'm clear on this point now. Thanks.

Now I'm trying to understand more on User Instance usage:

If not specified in the connection string, what is the default, yes or no?
For shared multiuser usage located on a server, should it be enabled or not?

I'm just unclear on when/when not to use it.

TIA ... Rick

|||If you don′t uise the user instance keyword you use SQL Server Exprtess just as a normal database. If you want to attach the database to a SQL Server instance you don′t need to use User instance availbility. Just attach the db to a SQL Servert (Express) and connect via the "normal* connection string to it (without specifying something like user instance)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Hi Rick,

Regarding User Instances:

If you don't specify it in the connection string, User Instances are not used.

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
--
Corey YoungCan MSDTC get through the XP Firewall?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>|||I turned the firewall off on both machines.
--
Corey Young
"Roger Wolter[MSFT]" wrote:
> Can MSDTC get through the XP Firewall?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
> news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
> >I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> > Server 2003 machine. I added the Windows Server 2003 machine as a linked
> > server on the Windows XP SP2 machine.
> >
> > I have checked and double checked that the DTC settings are correct on
> > both
> > machines and that the DTC is running on both machines, but I am unable to
> > execute a distributed transaction. I have even tried playing around with
> > many
> > different combinations of settings to try to get this to work. I have
> > followed the directions in many of the documents that can be found online
> > on
> > this issue, but without success.
> >
> > I am using SQL Server security and am able to execute queries if I do not
> > begin a transaction. But I cannot execute them if I begin a transaction.
> >
> > When I execute them in a transaction, I get the following error:
> >
> > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> > message
> > "No transaction is active.".
> >
> > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> >
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "linkedserver" was unable to begin a distributed
> > transaction.
> >
> > Does anyone have any ideas?
> >
> > --
> > Corey Young
> >
>
>

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey Young
Can MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with man
y
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey YoungCan MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>

Distributed Transaction takes far too long

Hi all,

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

I also tried to use the local server as linked server (loopback) but it
did not work either.

Any suggestions?

Thanks,
JoHi Jo

You don't really give much information to work on! I assume that running
forever means that it has not completed? Therefore you may want to profile
the query to see what is exactly happening. You may also want to try
OPENQUERY or OPENROWSET instead.

I would expect the loop back to fail as you can not use them in a
distributed transaction. From BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.John
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:cpm67p$t4d$1@.zeppelin.rz.uni-potsdam.de...
> Hi all,
> I would like to perform an
> INSERT INTO LINKEDSVR.dbo.xyz.abc
> SELECT ... FROM dbo.dfg
> where LINKEDSVR is a linked server on another machine. Both servers are
> running SQLServer 2000 and have the DTC running.
> When I run this batch from QueryAnalyzer without explicitly using
> transactions, it works well (takes about 5 sec) - however, when I
> enclose it using
> begin [distributed] tran/commit tran
> the query runs forever.
> I also tried to use the local server as linked server (loopback) but it
> did not work either.
> Any suggestions?
> Thanks,
> Jo|||Jo Siffert (jo.siffert@.gmx.net) writes:
> I would like to perform an
> INSERT INTO LINKEDSVR.dbo.xyz.abc
> SELECT ... FROM dbo.dfg
> where LINKEDSVR is a linked server on another machine. Both servers are
> running SQLServer 2000 and have the DTC running.
> When I run this batch from QueryAnalyzer without explicitly using
> transactions, it works well (takes about 5 sec) - however, when I
> enclose it using
> begin [distributed] tran/commit tran
> the query runs forever.

Have you checked for blocking? I have a recollection that distributed
transactions are by default seriliazable, which have more concurrency
problems.

Use sp_who (on both servers) and keep an eye on the Blk column. A non-
zero value in that column means that this spid is blocking the spid
on this row. And if this spid is your distributed transaction, you bave
the reason.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp