Remote ForXML and XML Result

I created a sp_addlinkedserver to SQL 2005. I sent a
Remote For XML Request through a SP on SQL2005. However, I
now remember SQL Server does not send XML result back
because I found out what I got was a bunch
of "0x4409530068006900700070006500720049004400440B430 06F006
D00700..."
What options do I have?
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:977101c478b7$e183dc40$a601280a@.phx.gbl...
[snip]
> What options do I have?
Put some kind of client in between.
Bryant
|||Bryant is right - SQL 2000 compatible FOR XML (top level with no TYPE
option) does not work directly between linked servers with either SQL 2000
or SQL 2005.
However, XML data type or NVARCHAR(MAX) will be fine when sent from a SQL
2005 linked server.
So, try using
FOR XML ..., TYPE --results in XML
or wrap SELECT ...FOR XML... into another SELECT:
SELECT (SELECT ... FOR XML...) -- results in NVARCHAR(MAX)
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:etFf6gMeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> "C TO" <anonymous@.discussions.microsoft.com> wrote in message
> news:977101c478b7$e183dc40$a601280a@.phx.gbl...
> [snip]
> Put some kind of client in between.
> --
> Bryant

Remote connection setup

Hi, new to remote SQL Connections. Hoping someone can help with a definitive tutorial for setting up remote connections in SQL2005. I have a SQLExpress installation at our office behind an ADSL router. I want to connect with SQL Server Management Studio from my home PC, which is also behind an ADSL router. What settings do I have to alter, both loal amd remote.

regards
OK fixed it myself. Nothing like trial and error to overcome a problem :)

remote connection error SQL2005

I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.

I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”

If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.

I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.

Is this a network issue, Windows2003 issue, or a SQL issue?

If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".

The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:

(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).

(2) For a "named" instance:

- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.

- grant firewall expection for the sqlservr.exe processes.

- start SQL Browser service on the server's machine.

- grant firewall expection for the sqlbrowser.exe processes.

|||This worked! Thank you!|||

I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***

Could you write out a complete connection string, pls? I tryed:

string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

|||

I am facing a similar problem.

I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:

Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Here is my connection string:

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

I have also used the Ip address

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.

Could you help me to track down the problem

|||

Shasur

This blog can help you.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

remote connection error SQL2005

I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.

I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”

If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.

I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.

Is this a network issue, Windows2003 issue, or a SQL issue?

If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".

The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:

(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).

(2) For a "named" instance:

- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.

- grant firewall expection for the sqlservr.exe processes.

- start SQL Browser service on the server's machine.

- grant firewall expection for the sqlbrowser.exe processes.

|||This worked! Thank you!|||

I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***

Could you write out a complete connection string, pls? I tryed:

string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

|||

I am facing a similar problem.

I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:

Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Here is my connection string:

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

I have also used the Ip address

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.

Could you help me to track down the problem

|||

Shasur

This blog can help you.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

remote connection error SQL2005

I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.

I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”

If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.

I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.

Is this a network issue, Windows2003 issue, or a SQL issue?

If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".

The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:

(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).

(2) For a "named" instance:

- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.

- grant firewall expection for the sqlservr.exe processes.

- start SQL Browser service on the server's machine.

- grant firewall expection for the sqlbrowser.exe processes.

|||This worked! Thank you!|||

I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***

Could you write out a complete connection string, pls? I tryed:

string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

|||

I am facing a similar problem.

I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:

Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Here is my connection string:

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

I have also used the Ip address

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.

Could you help me to track down the problem

|||

Shasur

This blog can help you.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

remote connection error SQL2005

I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.

I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”

If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.

I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.

Is this a network issue, Windows2003 issue, or a SQL issue?

If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".

The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:

(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).

(2) For a "named" instance:

- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.

- grant firewall expection for the sqlservr.exe processes.

- start SQL Browser service on the server's machine.

- grant firewall expection for the sqlbrowser.exe processes.

|||This worked! Thank you!|||

I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***

Could you write out a complete connection string, pls? I tryed:

string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";

|||

I am facing a similar problem.

I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:

Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Here is my connection string:

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

I have also used the Ip address

Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"

The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.

Could you help me to track down the problem

|||

Shasur

This blog can help you.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

remote connection error 26

I receive an error 26 "Error locating server/instance specified" when trying
to connect to a named instance of sql2005 through sql server mgmt studio (i
can connect locally just not from a remote pc). I can also connect via the
old query analyzer to the remote sql2005 server.
I have enabled remote connections in SQL on the server and my firewall is
allowing traffic through the port specified in the asconfig\msmdredir.ini
since I was able to telnet to it.
Also, the sqlbrowser service is running.
Any suggestions?
Are you using dynamic ports?
Start SQL Server Configuration Manager->Protocols for
XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
If you are try specifying the dynamic port number in the Server Name fileld
like this:
<server>\sqlexpress,<portnumber>
in mgmt studio
"matt" wrote:

> I receive an error 26 "Error locating server/instance specified" when trying
> to connect to a named instance of sql2005 through sql server mgmt studio (i
> can connect locally just not from a remote pc). I can also connect via the
> old query analyzer to the remote sql2005 server.
> I have enabled remote connections in SQL on the server and my firewall is
> allowing traffic through the port specified in the asconfig\msmdredir.ini
> since I was able to telnet to it.
> Also, the sqlbrowser service is running.
> Any suggestions?
|||Thanks for your suggestion. Yes I tried appending the port number to the
server\instance with no success.
What I ended up doing was to create another instance of SQL 2005 as a test
and I was able to connect to that without any problems.
I have a suspicion that one of the recent MS updates (security and/or
MSXML6) may have wrecked my orignal instance somehow since I have seen a
similar posting (although for older patches) elsewhere on the web and I did
not have any problems until after this past set of updates.
"Axel" wrote:
[vbcol=seagreen]
> Are you using dynamic ports?
> Start SQL Server Configuration Manager->Protocols for
> XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
> If you are try specifying the dynamic port number in the Server Name fileld
> like this:
> <server>\sqlexpress,<portnumber>
> in mgmt studio
>
> "matt" wrote:

remote connection error 26

I receive an error 26 "Error locating server/instance specified" when trying
to connect to a named instance of sql2005 through sql server mgmt studio (i
can connect locally just not from a remote pc). I can also connect via the
old query analyzer to the remote sql2005 server.
I have enabled remote connections in SQL on the server and my firewall is
allowing traffic through the port specified in the asconfig\msmdredir.ini
since I was able to telnet to it.
Also, the sqlbrowser service is running.
Any suggestions?Are you using dynamic ports?
Start SQL Server Configuration Manager->Protocols for
XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
If you are try specifying the dynamic port number in the Server Name fileld
like this:
<server>\sqlexpress,<portnumber>
in mgmt studio
"matt" wrote:

> I receive an error 26 "Error locating server/instance specified" when tryi
ng
> to connect to a named instance of sql2005 through sql server mgmt studio (
i
> can connect locally just not from a remote pc). I can also connect via th
e
> old query analyzer to the remote sql2005 server.
> I have enabled remote connections in SQL on the server and my firewall is
> allowing traffic through the port specified in the asconfig\msmdredir.ini
> since I was able to telnet to it.
> Also, the sqlbrowser service is running.
> Any suggestions?|||Thanks for your suggestion. Yes I tried appending the port number to the
server\instance with no success.
What I ended up doing was to create another instance of SQL 2005 as a test
and I was able to connect to that without any problems.
I have a suspicion that one of the recent MS updates (security and/or
MSXML6) may have wrecked my orignal instance somehow since I have seen a
similar posting (although for older patches) elsewhere on the web and I did
not have any problems until after this past set of updates.
"Axel" wrote:
[vbcol=seagreen]
> Are you using dynamic ports?
> Start SQL Server Configuration Manager->Protocols for
> XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
> If you are try specifying the dynamic port number in the Server Name filel
d
> like this:
> <server>\sqlexpress,<portnumber>
> in mgmt studio
>
> "matt" wrote:
>