Sql – How to access SQL Server 2005 FROM a guest XP OS running in Virtual PC

64-bitsql-server-2005virtual-pcwindows-xp

I am running as my Host OS – Win 7 x64 and running an instance of SQL Server 2005 (developer, x64). I am also running an instance of Virtual PC XP and am trying to connect to the Host OS Sql Instance without alot of luck.

I can ping the Host from the Guest OS and vice versa.

The Virtual PC Guest is not using the NAT network adapter, but the physical adapter of the machine.

I ran SQL Server Surface Area Configuration tool on the host and made sure both local and remote connections are allowed for both TCP/IP and Named pipes.

I installed the SQL Server 2005 Native client on the Guest XP OS as well as SQLCMD.

When I issue: "SQLCMD -L" I see:

Servers:
    ROBSWIN7BOX

However when I try "SQLCMD -S ROBSWIN7BOX", I get:

HResult 0x52E, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [1326].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection
s..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I've also tried various SQLCMD -S .. -U sa .. -P and SQLCMD -S … -E (SQL Server is configured for Mixed mode authentication).

Any ideas? What am I overlooking?

THanks!

Rob

Best Answer

Error 0x52e: (Win32) 0x52e (1326) - Logon failure: unknown user name or bad password.

You need Win auth to open the named pipe. Make sure SQL port is open by firewall on host and force tcp in your connection: SQLCDM -S tcp:ROBSWIN7BOX -U user -P pwd

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql-server – Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql-server – Check if table exists in SQL Server

Related Topic