Error reading a linked Excel spreadsheet

This problem just started happening (though the process had been working
without a problem for several months):
SQL to create link:
--if the link exists drop it
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'Excel')
EXEC master.dbo.sp_dropserver @.server=N'Excel', @.droplogins='droplogins'
--create the link
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@.FileName, NULL, 'Excel 5.0'
--add the login
EXEC sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
--SQL to read linked data (which throws below error):
EXEC sp_tables_ex Excel
--(this also throws same error)
select *
INTO ExcelData
from Excel...[' + @.SheetName + ']
The link to the excel spreadsheet can be made but when you try to read the
data you get:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have tried the following which are suggestions from searching the internet:
1. Making sure the registry entries are there.
2. Renaming Msexcl40.dll and the opening Access and running Detect and
Repair, which placed a new Msexcl40.dll in the system32 directory
3. Restarting the SQL Server Service and restarting the server
4. I tried different excel files
I have linked to the same files used above from my local SQL server and had
no problem reading the data.
I don’t know what else to do. Can someone help me with this problem?
Harolds
Hello Harolds,
From the error message, it seems jet driver has issues. You may want to
reinstall Jet SP8 to test:
829558.KB.EN-US Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/default...B;EN-US;829558
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
If the issue persists, please try to reinstall MDAC to see if it helps:
1. Find the file C:\windows\inf\mdac.inf (%SYSTEMROOT%\inf\mdac.inf)
(the INF folder is hidden so you will have to make in viewable:
click on Tools, Folder options..., View, click Show hidden files and
folders).
2. Right click on the file and choose install.
3. When prompted to get file, on the Locate File dialog box that results,
click Browse. You may want to direct to Win2003 SP1 setup CD or folders
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Error reading a linked Excel spreadsheet

This problem just started happening (though the process had been working
without a problem for several months):
SQL to create link:
--if the link exists drop it
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'Excel')
EXEC master.dbo.sp_dropserver @.server=N'Excel', @.droplogins='droplogins'
--create the link
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@.FileName, NULL, 'Excel 5.0'
--add the login
EXEC sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
--SQL to read linked data (which throws below error):
EXEC sp_tables_ex Excel
--(this also throws same error)
select *
INTO ExcelData
from Excel...[' + @.SheetName + ']
The link to the excel spreadsheet can be made but when you try to read the
data you get:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have tried the following which are suggestions from searching the internet
:
1. Making sure the registry entries are there.
2. Renaming Msexcl40.dll and the opening Access and running Detect and
Repair, which placed a new Msexcl40.dll in the system32 directory
3. Restarting the SQL Server Service and restarting the server
4. I tried different excel files
I have linked to the same files used above from my local SQL server and had
no problem reading the data.
I don’t know what else to do. Can someone help me with this problem?
HaroldsHello Harolds,
From the error message, it seems jet driver has issues. You may want to
reinstall Jet SP8 to test:
829558.KB.EN-US Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/defaul...KB;EN-US;829558
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/defaul...kb;en-us;239114
If the issue persists, please try to reinstall MDAC to see if it helps:
1. Find the file C:\windows\inf\mdac.inf (%SYSTEMROOT%\inf\mdac.inf)
(the INF folder is hidden so you will have to make in viewable:
click on Tools, Folder options..., View, click Show hidden files and
folders).
2. Right click on the file and choose install.
3. When prompted to get file, on the Locate File dialog box that results,
click Browse. You may want to direct to Win2003 SP1 setup CD or folders
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi guys !!!
I have the same problem, I'm using the same version of SQL, Excel & MDAC in
my local pc and the production server, however, the issue is only in the ser
ver.
Harolds: Please keep me posted about the way you solve this.
My Best Regards.|||I never got it fixed, I moved the process to another server where the error
does not occur.
--
Harolds
"velort" wrote:

> Hi guys !!!
> I have the same problem, I'm using the same version of SQL, Excel &
> MDAC in my local pc and the production server, however, the issue is
> only in the server.
> Harolds: Please keep me posted about the way you solve this.
> My Best Regards.
>
> --
> velort
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1450885.html
>

Error on setting ip partners : servers not found

Hello,

I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.

request on first :
ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://192.168.1.71:5050'

I get :

Msg1418, Niveau16, tat1, Ligne1
L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible à atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.

And it's the same problem with the second server.

I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) :
Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

Thanks for helping.

ps : Excuse my english ^^

The problem might be Fully Qualified Domain Name (FQDN).........try specifying as shown below,

To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is :

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

The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt :

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'
|||Thanks for you answer.

So we can't use IP to connect servers ? I thought the contrary...

I start to work with domain, I set same domain for each server, but I have another problem, the mirror server is OK (It takes the ALTER TABLE), but I can't set the PARTNER to master, I get the same error as before, and I don't understand why.

Thanks for help |||Arnard can you pls explain it a little more ? is database mirroring configured and workiong well ? do you mean to say that you can't set the "alter database set partner" for principal ?
|||Yes, when i do the command :

ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://srv-bat2.technet:5050'

on "master" and "slave", slave accept the command (with srv-bat instead of srv-bat2), but master not, he says that srv-bat2 is not found or not exists.

And I don't understand why master does't find slave, in contrary of slave that find master.
|||May be can you try giving someother end point listening port instead of 5050 you've mentioned above !
|||Result of sys.tcp_endpoints on srv-bat2 :

Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

So I have the good port :/
netstat says port 5050 is open.

I don't understand why I can't make the mirroring -_-
|||Arnard,
I have configured mirroring in sql 2005 evaluation edition pls refer the link,
http://sql-articles.com/articles/dbmrr.htm
i don't know if it will solve your problem but just have a glance through it

Thanxx
Deepak
|||Thanks for your link, but I already saw this website.

Howewer, when I used the GUI to create the mirror, on the master server the path selected by sql server is <host>.<domain>:<port>, but on the slave server, I have <host>:<port>. And when I tried to start mirroring, I get the same error saying that the second server is not found

I have switch between the two servers and I get the same result. Do I need to configure another thing anywhere ? the fact that the slave server's path is <host>:<port> instead of <host>.<domain>:<port> means that there is a bug on configuration, but I can't say what and where.

Thanks fr your help ^^
|||Must I create a new login such this pattern : CREATE LOGIN <domain\\login> FROM WINDOWS ?
When You create your instance of SQL Server, what do you set for account to run service ? Local System ? Network System ? domain ? Is this element important or interessant in my problem ? thx
|||Actually in the link i gave above i've created 3 instances of sql server in my local PC and all 3 run under local system account but in real time environment you need to make use of a domain id (preferably) for the sql service account in all 3 principal,mirror and witness servers

Thanxx
Deepak

|||I've tried with the elment domain, I set the login of my administrator account, the password and . for domain (. give the real domain), and I get the same problem, server not found. So the problem is not here

Somebody speaks me of sp_configure and surface area configuration, what do I search with this tools, an idea ?

Another question, how can I get the sql sever developer edition ?

|||refer the link for developer edition,
http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Thanxx
Deepak


|||Do I need to create login on each server for the connection, and give access to connect endpoint, or sa is ok ?
|||I believe you need to create same login in each server for the connection and configure endpoint ! !

Error on setting ip partners : servers not found

Hello,

I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.

request on first :
ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://192.168.1.71:5050'

I get :

Msg1418, Niveau16, tat1, Ligne1
L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible à atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.

And it's the same problem with the second server.

I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) :
Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

Thanks for helping.

ps : Excuse my english ^^

The problem might be Fully Qualified Domain Name (FQDN).........try specifying as shown below,

To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is :

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

The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt :

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'
|||Thanks for you answer.

So we can't use IP to connect servers ? I thought the contrary...

I start to work with domain, I set same domain for each server, but I have another problem, the mirror server is OK (It takes the ALTER TABLE), but I can't set the PARTNER to master, I get the same error as before, and I don't understand why.

Thanks for help |||Arnard can you pls explain it a little more ? is database mirroring configured and workiong well ? do you mean to say that you can't set the "alter database set partner" for principal ?
|||Yes, when i do the command :

ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://srv-bat2.technet:5050'

on "master" and "slave", slave accept the command (with srv-bat instead of srv-bat2), but master not, he says that srv-bat2 is not found or not exists.

And I don't understand why master does't find slave, in contrary of slave that find master.
|||May be can you try giving someother end point listening port instead of 5050 you've mentioned above !
|||Result of sys.tcp_endpoints on srv-bat2 :

Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

So I have the good port :/
netstat says port 5050 is open.

I don't understand why I can't make the mirroring -_-
|||Arnard,
I have configured mirroring in sql 2005 evaluation edition pls refer the link,
http://sql-articles.com/articles/dbmrr.htm
i don't know if it will solve your problem but just have a glance through it

Thanxx
Deepak
|||Thanks for your link, but I already saw this website.

Howewer, when I used the GUI to create the mirror, on the master server the path selected by sql server is <host>.<domain>:<port>, but on the slave server, I have <host>:<port>. And when I tried to start mirroring, I get the same error saying that the second server is not found

I have switch between the two servers and I get the same result. Do I need to configure another thing anywhere ? the fact that the slave server's path is <host>:<port> instead of <host>.<domain>:<port> means that there is a bug on configuration, but I can't say what and where.

Thanks fr your help ^^
|||Must I create a new login such this pattern : CREATE LOGIN <domain\\login> FROM WINDOWS ?
When You create your instance of SQL Server, what do you set for account to run service ? Local System ? Network System ? domain ? Is this element important or interessant in my problem ? thx
|||Actually in the link i gave above i've created 3 instances of sql server in my local PC and all 3 run under local system account but in real time environment you need to make use of a domain id (preferably) for the sql service account in all 3 principal,mirror and witness servers

Thanxx
Deepak

|||I've tried with the elment domain, I set the login of my administrator account, the password and . for domain (. give the real domain), and I get the same problem, server not found. So the problem is not here

Somebody speaks me of sp_configure and surface area configuration, what do I search with this tools, an idea ?

Another question, how can I get the sql sever developer edition ?

|||refer the link for developer edition,
http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Thanxx
Deepak


|||Do I need to create login on each server for the connection, and give access to connect endpoint, or sa is ok ?
|||I believe you need to create same login in each server for the connection and configure endpoint ! !

Error on setting ip partners : servers not found

Hello,

I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.

request on first :
ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://192.168.1.71:5050'

I get :

Msg1418, Niveau16, tat1, Ligne1
L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible à atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.

And it's the same problem with the second server.

I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) :
Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

Thanks for helping.

ps : Excuse my english ^^

The problem might be Fully Qualified Domain Name (FQDN).........try specifying as shown below,

To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is :

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

The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt :

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'
|||Thanks for you answer.

So we can't use IP to connect servers ? I thought the contrary...

I start to work with domain, I set same domain for each server, but I have another problem, the mirror server is OK (It takes the ALTER TABLE), but I can't set the PARTNER to master, I get the same error as before, and I don't understand why.

Thanks for help |||Arnard can you pls explain it a little more ? is database mirroring configured and workiong well ? do you mean to say that you can't set the "alter database set partner" for principal ?
|||Yes, when i do the command :

ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://srv-bat2.technet:5050'

on "master" and "slave", slave accept the command (with srv-bat instead of srv-bat2), but master not, he says that srv-bat2 is not found or not exists.

And I don't understand why master does't find slave, in contrary of slave that find master.
|||May be can you try giving someother end point listening port instead of 5050 you've mentioned above !
|||Result of sys.tcp_endpoints on srv-bat2 :

Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

So I have the good port :/
netstat says port 5050 is open.

I don't understand why I can't make the mirroring -_-
|||Arnard,
I have configured mirroring in sql 2005 evaluation edition pls refer the link,
http://sql-articles.com/articles/dbmrr.htm
i don't know if it will solve your problem but just have a glance through it

Thanxx
Deepak
|||Thanks for your link, but I already saw this website.

Howewer, when I used the GUI to create the mirror, on the master server the path selected by sql server is <host>.<domain>:<port>, but on the slave server, I have <host>:<port>. And when I tried to start mirroring, I get the same error saying that the second server is not found

I have switch between the two servers and I get the same result. Do I need to configure another thing anywhere ? the fact that the slave server's path is <host>:<port> instead of <host>.<domain>:<port> means that there is a bug on configuration, but I can't say what and where.

Thanks fr your help ^^
|||Must I create a new login such this pattern : CREATE LOGIN <domain\\login> FROM WINDOWS ?
When You create your instance of SQL Server, what do you set for account to run service ? Local System ? Network System ? domain ? Is this element important or interessant in my problem ? thx
|||Actually in the link i gave above i've created 3 instances of sql server in my local PC and all 3 run under local system account but in real time environment you need to make use of a domain id (preferably) for the sql service account in all 3 principal,mirror and witness servers

Thanxx
Deepak

|||I've tried with the elment domain, I set the login of my administrator account, the password and . for domain (. give the real domain), and I get the same problem, server not found. So the problem is not here

Somebody speaks me of sp_configure and surface area configuration, what do I search with this tools, an idea ?

Another question, how can I get the sql sever developer edition ?

|||refer the link for developer edition,
http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Thanxx
Deepak


|||Do I need to create login on each server for the connection, and give access to connect endpoint, or sa is ok ?
|||I believe you need to create same login in each server for the connection and configure endpoint ! !

Error on setting ip partners : servers not found

Hello,

I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.

request on first :
ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://192.168.1.71:5050'

I get :

Msg1418, Niveau16, tat1, Ligne1
L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible à atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.

And it's the same problem with the second server.

I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) :
Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

Thanks for helping.

ps : Excuse my english ^^

The problem might be Fully Qualified Domain Name (FQDN).........try specifying as shown below,

To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is :

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

The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt :

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'
|||Thanks for you answer.

So we can't use IP to connect servers ? I thought the contrary...

I start to work with domain, I set same domain for each server, but I have another problem, the mirror server is OK (It takes the ALTER TABLE), but I can't set the PARTNER to master, I get the same error as before, and I don't understand why.

Thanks for help |||Arnard can you pls explain it a little more ? is database mirroring configured and workiong well ? do you mean to say that you can't set the "alter database set partner" for principal ?
|||Yes, when i do the command :

ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://srv-bat2.technet:5050'

on "master" and "slave", slave accept the command (with srv-bat instead of srv-bat2), but master not, he says that srv-bat2 is not found or not exists.

And I don't understand why master does't find slave, in contrary of slave that find master.
|||May be can you try giving someother end point listening port instead of 5050 you've mentioned above !
|||Result of sys.tcp_endpoints on srv-bat2 :

Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

So I have the good port :/
netstat says port 5050 is open.

I don't understand why I can't make the mirroring -_-
|||Arnard,
I have configured mirroring in sql 2005 evaluation edition pls refer the link,
http://sql-articles.com/articles/dbmrr.htm
i don't know if it will solve your problem but just have a glance through it

Thanxx
Deepak
|||Thanks for your link, but I already saw this website.

Howewer, when I used the GUI to create the mirror, on the master server the path selected by sql server is <host>.<domain>:<port>, but on the slave server, I have <host>:<port>. And when I tried to start mirroring, I get the same error saying that the second server is not found

I have switch between the two servers and I get the same result. Do I need to configure another thing anywhere ? the fact that the slave server's path is <host>:<port> instead of <host>.<domain>:<port> means that there is a bug on configuration, but I can't say what and where.

Thanks fr your help ^^
|||Must I create a new login such this pattern : CREATE LOGIN <domain\\login> FROM WINDOWS ?
When You create your instance of SQL Server, what do you set for account to run service ? Local System ? Network System ? domain ? Is this element important or interessant in my problem ? thx
|||Actually in the link i gave above i've created 3 instances of sql server in my local PC and all 3 run under local system account but in real time environment you need to make use of a domain id (preferably) for the sql service account in all 3 principal,mirror and witness servers

Thanxx
Deepak

|||I've tried with the elment domain, I set the login of my administrator account, the password and . for domain (. give the real domain), and I get the same problem, server not found. So the problem is not here

Somebody speaks me of sp_configure and surface area configuration, what do I search with this tools, an idea ?

Another question, how can I get the sql sever developer edition ?

|||refer the link for developer edition,
http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Thanxx
Deepak


|||Do I need to create login on each server for the connection, and give access to connect endpoint, or sa is ok ?
|||I believe you need to create same login in each server for the connection and configure endpoint ! !
sql

Error on setting ip partners : servers not found

Hello,

I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.

request on first :
ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://192.168.1.71:5050'

I get :

Msg1418, Niveau16, tat1, Ligne1
L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible à atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.

And it's the same problem with the second server.

I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) :
Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

Thanks for helping.

ps : Excuse my english ^^

The problem might be Fully Qualified Domain Name (FQDN).........try specifying as shown below,

To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is :

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

The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt :

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'
|||Thanks for you answer.

So we can't use IP to connect servers ? I thought the contrary...

I start to work with domain, I set same domain for each server, but I have another problem, the mirror server is OK (It takes the ALTER TABLE), but I can't set the PARTNER to master, I get the same error as before, and I don't understand why.

Thanks for help |||Arnard can you pls explain it a little more ? is database mirroring configured and workiong well ? do you mean to say that you can't set the "alter database set partner" for principal ?
|||Yes, when i do the command :

ALTER DATABASE [panorama2]
SET PARTNER = N'TCP://srv-bat2.technet:5050'

on "master" and "slave", slave accept the command (with srv-bat instead of srv-bat2), but master not, he says that srv-bat2 is not found or not exists.

And I don't understand why master does't find slave, in contrary of slave that find master.
|||May be can you try giving someother end point listening port instead of 5050 you've mentioned above !
|||Result of sys.tcp_endpoints on srv-bat2 :

Mirroring 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL

So I have the good port :/
netstat says port 5050 is open.

I don't understand why I can't make the mirroring -_-
|||Arnard,
I have configured mirroring in sql 2005 evaluation edition pls refer the link,
http://sql-articles.com/articles/dbmrr.htm
i don't know if it will solve your problem but just have a glance through it

Thanxx
Deepak
|||Thanks for your link, but I already saw this website.

Howewer, when I used the GUI to create the mirror, on the master server the path selected by sql server is <host>.<domain>:<port>, but on the slave server, I have <host>:<port>. And when I tried to start mirroring, I get the same error saying that the second server is not found

I have switch between the two servers and I get the same result. Do I need to configure another thing anywhere ? the fact that the slave server's path is <host>:<port> instead of <host>.<domain>:<port> means that there is a bug on configuration, but I can't say what and where.

Thanks fr your help ^^
|||Must I create a new login such this pattern : CREATE LOGIN <domain\\login> FROM WINDOWS ?
When You create your instance of SQL Server, what do you set for account to run service ? Local System ? Network System ? domain ? Is this element important or interessant in my problem ? thx
|||Actually in the link i gave above i've created 3 instances of sql server in my local PC and all 3 run under local system account but in real time environment you need to make use of a domain id (preferably) for the sql service account in all 3 principal,mirror and witness servers

Thanxx
Deepak

|||I've tried with the elment domain, I set the login of my administrator account, the password and . for domain (. give the real domain), and I get the same problem, server not found. So the problem is not here

Somebody speaks me of sp_configure and surface area configuration, what do I search with this tools, an idea ?

Another question, how can I get the sql sever developer edition ?

|||refer the link for developer edition,
http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Thanxx
Deepak


|||Do I need to create login on each server for the connection, and give access to connect endpoint, or sa is ok ?
|||I believe you need to create same login in each server for the connection and configure endpoint ! !

error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>