Automating SQL Setup.

Is there a way that I can Automate SQL Setup via C#, PERL or VB?Hi

When you install SQLServer one of the Advanced options is to record a setup
script. This will allow you to do unattanded installs. There would be no
pressing reason to use a program to run this unless but you could probably
do it by spawning a process.

John

"KP" <snazystyl@.hotmail.com> wrote in message
news:dc2b0830.0310141453.3d489efb@.posting.google.c om...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?|||snazystyl@.hotmail.com (KP) wrote in message news:<dc2b0830.0310141453.3d489efb@.posting.google.com>...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?

See the sections "Performing an Unattended Installation" and
"Installing Desktop Engine" in Books Online, depending on whether you
want to install the full version of MSSQL, or MSDE.

Simon

Automating MSDE Setup

On microsoft support web site
"http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I saw the
article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE Setup with the
Package and Deployment Wizard (PDW)" about automation of MSDE setup from PDW
of Visual Basic but only setup1.exe is downloadble. I can't use it because my
setup1.vbp is already customized for my application. I can't find a location
to download a source code of setup1.exe for merge it with my custom
setup1.vbp.
Please help me!
Thanks in advance.
hi Terenzio,
"Lingua Terenzio" <Lingua Terenzio@.discussions.microsoft.com> ha
scritto nel messaggio
news:0954BE29-F0A0-495A-AA4A-B3525941C718@.microsoft.com
> On microsoft support web site
> "http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I
> saw the article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE
> Setup with the Package and Deployment Wizard (PDW)" about automation
> of MSDE setup from PDW of Visual Basic but only setup1.exe is
> downloadble. I can't use it because my setup1.vbp is already
> customized for my application. I can't find a location to download a
> source code of setup1.exe for merge it with my custom setup1.vbp.
> Please help me!
> Thanks in advance.
have a look at
http://msdn.microsoft.com/vstudio/do...instdwnld.aspx
it provides the full VB6 code for installing MSDE 1.0 ... you have to
customize it a little to support MSDE 2000 in order to manage all MSDE 2000
setup.exe boostrapper installer parameters...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Automatically setup table from dataset

I need to add tables to an existing "report" from datasets that have 50+
columns. Each table is a different dataset. Is there anyway to replicte the
behiour of the wizard on an existing report? Adding columns one at a time is
a pain, it'd be easier for me to add them all and then remove the few I don't
need.
And yes I know that db tables that wide and reports with this much info are
somewhat useless but I'm forced to swim in the pond I'm in.Can't you just copy and paste a previously created table? It will maintain
all the properties of the table.
>I need to add tables to an existing "report" from datasets that have 50+
> columns. Each table is a different dataset. Is there anyway to replicte
> the
> behiour of the wizard on an existing report? Adding columns one at a time
> is
> a pain, it'd be easier for me to add them all and then remove the few I
> don't
> need.
> And yes I know that db tables that wide and reports with this much info
> are
> somewhat useless but I'm forced to swim in the pond I'm in.

automatically copy database to another pc

When I create a setup program for my vb.net 1.0 app which has embedded sql express database, it successfully copies the database to the new machine alone with the upgraded app.

However, if I just copy the vb.exe app to the other pc and also copy the mdf, ldf files, I get an error opening the sql database.

I am presuming that the setup program does some kind of backup restore or detach, attach to copy in the .mdf.

If the user cannot do this from the management studio (or if they do not have the management studio installed), is there any way I can create some method to have this copying done automatically via some code by the user?

Thanks

SM Haig

Yes, you can run a restore script using the sqlcmd program. You can read more about the restore command here:

http://msdn2.microsoft.com/en-us/library/ms186858.aspx

Buck Woody

Automatic sync between SQLce and Access

Hi,

I am converting an old eVB application to CF.NET 2. It's a very simple setup.
1) An Access database sits on the host and has occasional edits applied to it.
2) An application on the PDA with a mirror copy of the Access database (filtered data) and also has occasional edits applied
3) When the user puts the PDA in the cradle the databases get automatically synchronized with each other.

Probably couldn't get much simpler than this. This used to be very easy to do with PocketAccess and ActiveSync. Unfortunately this is no longer supported. I decided to use the new SQLce database instead when porting to CF.NET. It's working like a charm except that I haven't found an easy way to synchronize with the desktop Access application.

Merge replication seems to be exactly what I need but it doesn't work with Access or SQL Server Express. I don't need (or want) a full version of SQL Server. The dataset is very small.

ADS seems to point in the right direction except it doesn't support automatic synchronization. It also doesn't support Vista in combination with PPC2003SE.

Sync Services seems to have the same problems as ADS.

I have seen some third party tools around but they all require the user to manually kick off synchronization which I'm desperatly trying to avoid.

I've been searching for days now and I'm not getting any closer to a solution. Any help would be greatly appreciated!

Cheers,
Thomas

Hi Thomas,

You can kick of a process on the PC (from the desktop PC) (your own program), whenever ActiveSync is connected, and then use RAPI to launch a process on the device. You can even add command line parameters to the PDA process.

The activesync registry key is:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnConnect]

and opennetcf.org has a .NET RAPI wrapper: http://www.opennetcf.com/library/communication/OpenNETCF.Desktop.Communication.RAPI.CreateProcess.html

|||

Thanks for the reply. This is indeed a step in the right direction.

I guess the idea then would be to use ADS on top of it. So cradling the PDA would create 2 processes, one to start the ADS service and one to start some custom synchronization application on the PDA which in turn would connect to the ADS desktop service using RDA.

Sounds very clunky to me, but it could work. I guess I was hoping there would be an easy solution. I mean, i can't imagine a much simpler scenario and it's not supported out of the box (anymore). Why does Microsoft not support this?

|||

ADS is the replacement technology for CEDB/ADOCE - MS Access sync - as described here: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/sync-with-access.aspx

It looks like ADS does do automatic sync on desktop connection:

Synchronization happens when there is an active ActiveSync connection between the device and the desktop.

(from http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/microsoft-sql-server-2005-compact-edition-access-database-synchronizer-ads-rtw.aspx)

|||

That's what I thought too. Unfortunately I haven't found any evidence that it actually does so. ADS documentation is skimpy at the moment, but from what I gather you have to create a PDA application that uses RDA to sync yourself. And the only thing it does automatic is start the desktop "listen" service. There is no "client agent". This is not "simple and easy" to do since you have to push and pull data manually (using RDA) and provide your own conflict handling! ADS as it is now is definitely NOT a replacement.

|||

*bump*

Don't tell me that's it?

|||

This MS blog post addresses your question:
http://blogs.msdn.com/sqlservercompact/archive/2007/02/22/ads-automatically-starting-the-synchronization-between-device-and-desktop.aspx

sql

automatic process

I need to automatically generate via SQL, export to Excel and e-mailed
to other people montly. what should I setup in the sql server?Much too vague to do anything but guess at an answer.
One wild idea is a Task that launches Excel which in turn runs a stored
procedure to return the results set into excel. Another task could send the
email with attached file.
Could also run a process from the OS level , running the query output into
excel and then email it.

"TedJM" <ted_gear@.hotmail.com> wrote in message
news:55b4f192.0412011101.15038fe4@.posting.google.c om...
>I need to automatically generate via SQL, export to Excel and e-mailed
> to other people montly. what should I setup in the sql server?|||TedJM,

Take a look at Gadami: http://www.kripsoft.com. It's a program (EXE)
that lets you output the results of one or more SQL queries to Excel
and email them using an available SMTP server.

-Krip|||The best way is to create a DTS package, that uses a query to output a
file with a pre-defined global variable as the path and file anme and
use sql mail to generate an email with an attachment.... And schedule
this DTS in a sql job...!

Automatic Identity Range Management

Hello,
I've got a question dealing with automatic identity range management.
Currently we have a database setup for publication in which the subscribers
are running SQL Server CE on mobile devices. We've got automatic identity
range management turned on for a few of our tables and we're having some
issues with the publisher identity ranges not getting automatically
reassigned after they are exhausted. I found this statement in the SQL Server
manuals:
"If the Publisher exhausts its identity range after an insert, it can
automatically assign a new range if the insert was performed by a member of
the db_owner fixed database role."
The user that is inserting the rows into the publisher database is a member
of the db_owner fixed database role but when a row is inserted which exhausts
the currenty identity range they are not automatically reassigned. I can
manually adjust the identity ranges using sp_adjustpublisheridentityrange
logged in with the same user, but they are not automatically adjusted after
the insert as the documentation suggests.
What else should we check for?
Thanks in advance,
mike...
You have to size the ranges for the max amount of inserts that would occur
between syncs. Otherwise the range will not be adjusted.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike..." <Mike@.discussions.microsoft.com> wrote in message
news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
> Hello,
> I've got a question dealing with automatic identity range management.
> Currently we have a database setup for publication in which the
> subscribers
> are running SQL Server CE on mobile devices. We've got automatic identity
> range management turned on for a few of our tables and we're having some
> issues with the publisher identity ranges not getting automatically
> reassigned after they are exhausted. I found this statement in the SQL
> Server
> manuals:
> "If the Publisher exhausts its identity range after an insert, it can
> automatically assign a new range if the insert was performed by a member
> of
> the db_owner fixed database role."
> The user that is inserting the rows into the publisher database is a
> member
> of the db_owner fixed database role but when a row is inserted which
> exhausts
> the currenty identity range they are not automatically reassigned. I can
> manually adjust the identity ranges using sp_adjustpublisheridentityrange
> logged in with the same user, but they are not automatically adjusted
> after
> the insert as the documentation suggests.
> What else should we check for?
> Thanks in advance,
> mike...
>
|||Hilary, Thanks for the reply - the problem we've run into is that the user
accounts (Windows Auth) sync'ing the devices are not members of the db_owner
role so the merge agents do not refresh the identity ranges (which is another
issues we've been attempting to work around). From our experiences (and from
reading the SQL Server manuals) the publisher identity ranges should refresh
themselves if the inserts are done at the publisher by users in the db_owner
fixed database role. This isn't occuring at one of our sites and I'm
wondering what other security arrangements need to be made for this to occur.
Any ideas?
Thanks again,
mike...
"Hilary Cotter" wrote:

> You have to size the ranges for the max amount of inserts that would occur
> between syncs. Otherwise the range will not be adjusted.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike..." <Mike@.discussions.microsoft.com> wrote in message
> news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
>
>

Automatic Failover Problem

Setup Configuration:

3 servers
- PRINCIPAL IP: 10.2.5.31 - DNS Lookup: db-server-2.mosside.choruscall.com
- MIRROR IP: 10.2.5.30 - DNS Lookup: sql-mirror.mosside.choruscall.com
- WITNESS ip: 10.2.5.32 - DNS Lookup: sql-witness.mosside.choruscall.com

Each Server is running Windows Server 2003 Enterprise Edition with SQL Server 2005 Enterprise Edition.
All server instances are enabled for remote connections(By default they are not).
All servers have the flag 1400 traceon and have been restarted.
PORT 5022 is unrestricted on network.

The server instances are connecting via certificates. Each server has an endpoint for the certificates to to connect on.

Certificate Setup Proceedure:

Principal_Host:
1. Create Master Key with Password

2. Create certificate with subject

3. Create endpoint for certificate (Listener_Port = 5022, Listener_ip = all)
to connect on for database_mirroring

4. Backup Certificate (principal_cert.cer)

5. Take backed up certificate to Mirror_Host

(Reapeat Steps 1-5 for Witness and Mirror)

Mirror_Host: Create Certificate on Mirror_Host for inbound connections from Principal:

6.(On Mirror_Host) Create Login for Principal using same password in step 1 (principal_login)

7. Create user for login just created. (principal_user)

8. Create local certificate for Principal on Mirror using certificate generated by principal.

ex: Create Certificate Principal_cert Authorization Principal_user FROM FILE='c:\principal_cert.cer'

9. (If an endpoint has been created already on the mirror)Grant connectiion to the login:

ex: Grant connect on endpoint::mirror_endpoint to principal_login

Repeat Steaps 6-9 for Principal and Witness Servers accordingly.

10. Import Database to SQL Server 2005 Principal Instance

11. Backup Database to disk with format

12. Backup Database log file to disk with format

13. Copy backups to mirror

14. Restore Database and log file with norecovery on Mirror_Host

15. Configre Database for Database Mirroring on Principal Server
There are two ways to do this. Via the wizzard or via the Transact-SQL window.
Using the wizzard appears to work since I started using FQDN.

PROBLEM:

After configuration, everythig appears to be correct. That is, the principal displays
that it is the principal and it is synchronized with the mirror. The mirror also displays that it is the
mirror and it is synchronized with the principal and it is in recovery. If I failover manually, the mirror
becomes the principal and the principal becomes the mirror (They form a quarum). If I disconnect the principal
from the network, the mirror is supposed to form a quarum with the witness and promote itself to principal status.
This is not what is happening. The witness recognizes that the principal is down and logs that info into its log file.
The Mirror attempts to contact the witness but cannot log onto the machine. The Mirror Logs the following:

Error: 1438, Severity: 16, State: 2.
The server instance Witness rejected configure request; read its error log file for more information.
The reason 1451, and state 3, can be of use for diagnostics by Microsoft.
This is a transient error hence retrying the request is likely to succeed.
Correct the cause if any and retry.

<<<<<<<MIRROR SERVER >>>>>>>>

2007-09-06 15:08:45.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:08:45.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:05.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:05.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:25.33 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:25.33 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:45.34 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:45.34 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:05.35 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:10:05.35 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:25.36 spid23s Error: 1438, Severity: 16, State: 2.

<<<<<<< WITNESS SERVER >>>>>>>>

2007-09-06 14:19:55.90 spid52 The Database Mirroring protocol transport is now listening for connections.
2007-09-06 15:07:11.64 spid24s Error: 1479, Severity: 16, State: 1.
2007-09-06 15:07:11.64 spid24s The mirroring connection to "TCP://db-server-2:5022" has timed out for database "APS_SQL_DEV" after 10 seconds without a response. Check the service and network connections.
2007-09-06 15:07:43.20 Server Error: 1474, Severity: 16, State: 1.
2007-09-06 15:07:43.20 Server Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://db-server-2:5022'.
2007-09-06 15:08:06.03 spid9s Error: 1474, Severity: 16, State: 1.
2007-09-06 15:08:06.03 spid9s Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://db-server-2:5022'.

hello,

from the logs it looks like you are not using the fully-qualified domain name (fqdn) when you are establishing the mirroring sessions. this is most likely the reason for the lack of connectivity.

when all the partners are up, you can use the sys.database_mirroring views on the principal and the mirror to figure out whether they both can talk to the witness (when all three nodes are up). if all connections are up, then automatic failover should happen. in your case most probably the mirror's connection to the witness will be shown as "disconnected", but if you use the fqdns it should work.

hth,

kaloian.

|||Trying giving the fully qualified domain name and see if it works as shown below,

IPCONFIG /ALL
Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.
On the mirror server, you would just repeat the same command, but with the principal server named :

ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://A.corp.mycompany.com:5022'

On the principal server, you next specify the witness server:

ALTER DATABASE [AdventureWorks] SET WITNESS =
N'TCP://W.corp.mycompany.com:5026'

|||

Thank for replying. I appreciate it very much.

I had to add the FQDN in the host file in Windows->system32->drivers->etc->host on the mirror server and principal server inatnaces and I restarted the witness server after I setup failover. Once I did all that...it worked.

Thanks Again!

Chris

Automatic Failover Problem

Setup Configuration:

3 servers
- PRINCIPAL IP: 10.2.5.31 - DNS Lookup: db-server-2.mosside.choruscall.com
- MIRROR IP: 10.2.5.30 - DNS Lookup: sql-mirror.mosside.choruscall.com
- WITNESS ip: 10.2.5.32 - DNS Lookup: sql-witness.mosside.choruscall.com

Each Server is running Windows Server 2003 Enterprise Edition with SQL Server 2005 Enterprise Edition.
All server instances are enabled for remote connections(By default they are not).
All servers have the flag 1400 traceon and have been restarted.
PORT 5022 is unrestricted on network.

The server instances are connecting via certificates. Each server has an endpoint for the certificates to to connect on.

Certificate Setup Proceedure:

Principal_Host:
1. Create Master Key with Password

2. Create certificate with subject

3. Create endpoint for certificate (Listener_Port = 5022, Listener_ip = all)
to connect on for database_mirroring

4. Backup Certificate (principal_cert.cer)

5. Take backed up certificate to Mirror_Host

(Reapeat Steps 1-5 for Witness and Mirror)

Mirror_Host: Create Certificate on Mirror_Host for inbound connections from Principal:

6.(On Mirror_Host) Create Login for Principal using same password in step 1 (principal_login)

7. Create user for login just created. (principal_user)

8. Create local certificate for Principal on Mirror using certificate generated by principal.

ex: Create Certificate Principal_cert Authorization Principal_user FROM FILE='c:\principal_cert.cer'

9. (If an endpoint has been created already on the mirror)Grant connectiion to the login:

ex: Grant connect on endpoint::mirror_endpoint to principal_login

Repeat Steaps 6-9 for Principal and Witness Servers accordingly.

10. Import Database to SQL Server 2005 Principal Instance

11. Backup Database to disk with format

12. Backup Database log file to disk with format

13. Copy backups to mirror

14. Restore Database and log file with norecovery on Mirror_Host

15. Configre Database for Database Mirroring on Principal Server
There are two ways to do this. Via the wizzard or via the Transact-SQL window.
Using the wizzard appears to work since I started using FQDN.

PROBLEM:

After configuration, everythig appears to be correct. That is, the principal displays
that it is the principal and it is synchronized with the mirror. The mirror also displays that it is the
mirror and it is synchronized with the principal and it is in recovery. If I failover manually, the mirror
becomes the principal and the principal becomes the mirror (They form a quarum). If I disconnect the principal
from the network, the mirror is supposed to form a quarum with the witness and promote itself to principal status.
This is not what is happening. The witness recognizes that the principal is down and logs that info into its log file.
The Mirror attempts to contact the witness but cannot log onto the machine. The Mirror Logs the following:

Error: 1438, Severity: 16, State: 2.
The server instance Witness rejected configure request; read its error log file for more information.
The reason 1451, and state 3, can be of use for diagnostics by Microsoft.
This is a transient error hence retrying the request is likely to succeed.
Correct the cause if any and retry.

<<<<<<<MIRROR SERVER >>>>>>>>

2007-09-06 15:08:45.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:08:45.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:05.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:05.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:25.33 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:25.33 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:45.34 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:45.34 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:05.35 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:10:05.35 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:25.36 spid23s Error: 1438, Severity: 16, State: 2.

<<<<<<< WITNESS SERVER >>>>>>>>

2007-09-06 14:19:55.90 spid52 The Database Mirroring protocol transport is now listening for connections.
2007-09-06 15:07:11.64 spid24s Error: 1479, Severity: 16, State: 1.
2007-09-06 15:07:11.64 spid24s The mirroring connection to "TCP://db-server-2:5022" has timed out for database "APS_SQL_DEV" after 10 seconds without a response. Check the service and network connections.
2007-09-06 15:07:43.20 Server Error: 1474, Severity: 16, State: 1.
2007-09-06 15:07:43.20 Server Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://db-server-2:5022'.
2007-09-06 15:08:06.03 spid9s Error: 1474, Severity: 16, State: 1.
2007-09-06 15:08:06.03 spid9s Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://db-server-2:5022'.

hello,

from the logs it looks like you are not using the fully-qualified domain name (fqdn) when you are establishing the mirroring sessions. this is most likely the reason for the lack of connectivity.

when all the partners are up, you can use the sys.database_mirroring views on the principal and the mirror to figure out whether they both can talk to the witness (when all three nodes are up). if all connections are up, then automatic failover should happen. in your case most probably the mirror's connection to the witness will be shown as "disconnected", but if you use the fqdns it should work.

hth,

kaloian.

|||Trying giving the fully qualified domain name and see if it works as shown below,

IPCONFIG /ALL
Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.
On the mirror server, you would just repeat the same command, but with the principal server named :

ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://A.corp.mycompany.com:5022'

On the principal server, you next specify the witness server:

ALTER DATABASE [AdventureWorks] SET WITNESS =
N'TCP://W.corp.mycompany.com:5026'

|||

Thank for replying. I appreciate it very much.

I had to add the FQDN in the host file in Windows->system32->drivers->etc->host on the mirror server and principal server inatnaces and I restarted the witness server after I setup failover. Once I did all that...it worked.

Thanks Again!

Chris

automatic disconnect inactive users

How can I setup SQLServer 2000 to automatically disconnect clients that have
been inactive for 2 hours? There was an option to do this in Sybase
SQLAnywhere, but I haven't found it SQLServer.
Thanks.
Hi
Nothing in SQL. If you need to do this, you need to write code that call the
KILL function.
The system stored procedure sp_who2 is a good code base to use.
Regards
Mike
"Daryl A." wrote:

> How can I setup SQLServer 2000 to automatically disconnect clients that have
> been inactive for 2 hours? There was an option to do this in Sybase
> SQLAnywhere, but I haven't found it SQLServer.
> Thanks.

automatic change of permission on database after install web site

I have Web Site with ASPNETDB database and own database. I was precompiled this site and I created aplication with Web Setup Project. This setup aplication includes SQL 2005 EXPRESS. When I installed and ran an my web site in Internet explorer, that displayed error "Failed to update database ... because the database is read-only". I know, that is a bug with permisson and the fix a bug is solution for this problem but i have installation package and i need automatic fix in setup application. Thanx for comments.

Vitty,

The main focus of this forum is on connectivty, client odbc/oledb drivers and etc. The expert on your issue might not visit this forum often. So, I suggest you to visite sql setup forum instead.

Autoimport from xls to SQL 2000?

I have a program setup the exports check request data into a xls file every
week on a shared directory on Windows 2003 server. I'd like a way to import
that data into SQL 2000 on a weekly basis. (Shared directory and SQL on same
server).
Ideas?
just create a dts job to do it.
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:42DDB5A3-0F75-4ABE-B7D5-CBA86251AC6C@.microsoft.com...
>I have a program setup the exports check request data into a xls file every
> week on a shared directory on Windows 2003 server. I'd like a way to
> import
> that data into SQL 2000 on a weekly basis. (Shared directory and SQL on
> same
> server).
> Ideas?
>

auto_increment_offset

I need to setup a way to manage auto_inc keys on MSSQL without the
distributor handling it. I am using MSSQL 2005 transactional replication
with updateable subscriptions.
I have done this with MySQL using auto_increment_offset
see article:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
if you want more info about the process.
Can MSSQL do anything like this?
Thanks
You can use dbcc checkident for this. It is better to let replication handle
it through automatic identity range mangement. Here is an article on it.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
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
"Sintel Silverblade" <sintel@.segamer.com> wrote in message
news:e5%235uVhqHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I need to setup a way to manage auto_inc keys on MSSQL without the
> distributor handling it. I am using MSSQL 2005 transactional replication
> with updateable subscriptions.
> I have done this with MySQL using auto_increment_offset
> see article:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> if you want more info about the process.
> Can MSSQL do anything like this?
> Thanks
>