Unable to load sample DB to SQL 2005

Question

Unable to load sample DB to SQL 2005

Wylbur

SSC-Addicted

Points: 403
More actions
March 15, 2010 at 9:43 pm

#386508

Hi all;
I am trying to load the "pubs" sample database to MS SQL 2005 using the osql utility,
and I am having some problems.
I tried the command with an active server instance specified
within the command,
osql -E -S SAM-V01\\SQL2005D-01 -Q "EXEC sp_attach_db N'pubs' N'c:\SQL Server 2000 Sample Databases\pubs.mdf'"
I got the following error:
-------------------------------------------------------------------
[SQL Native Client]Client unable to establish connection due to prelogin
failure
-------------------------------------------------------------------
I tried the command without an active server instance being specified
within the command,
osql -E -Q "EXEC sp_attach_db N'pubs' N'c:\SQL Server 2000 Sample Databases\pubs.mdf'"
I got the following error:
-------------------------------------------------------------------
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
Server [2].
[SQL Native Client]Login timeout expired
[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 connections.
-------------------------------------------------------------------
In Server properties > Connections,
"Allow remote connections to this server" is checked
Can anyone tell me what I am missing here?
Thanks!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

Wylbur (3/15/2010)
I am trying to load the "pubs" sample database to MS SQL 2005 using the osql utility

Just out of interest...why the old samples, and why the deprecated osql utility? The replacement command-line utility is sqlcmd. The new sample databases can be downloaded here.

I tried the command with an active server instance specified within the command, osql -E -S SAM-V01\\SQL2005D-01 -Q "EXEC sp_attach_db N'pubs' N'c:\SQL Server 2000 Sample Databases\pubs.mdf'"

There should only be one backslash there. Is SAM-V01\SQL2005D-01 on the local machine?

sp_attach_db is also deprecated I'm afraid. Use CREATE DATABASE...FOR ATTACH instead.

In fact you might find it easier to attach this sample database from SQL Server Management Studio. In Object Explorer, right-click Databases, and choose Attach...

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Wylbur SSC-Addicted Points: 403 More actions · Answer 2

Hi Paul;

Paul White (3/16/2010)
Wylbur (3/15/2010)
I am trying to load the "pubs" sample database to MS SQL 2005 using the osql utility
Just out of interest...why the old samples, and why the deprecated osql utility?

I'm working my way through a walkthrough tutorial within the documentation for

Visual Studio 2008. Apparently, this tutorial has been carried over from previous

versions, and no one has bothered to modernize the tutorial.

Paul White (3/16/2010)
The replacement command-line utility is sqlcmd. The new sample databases can be downloaded here.

Thanks - got it.

Paul White (3/16/2010)
I tried the command with an active server instance specified within the command, osql -E -S SAM-V01\\SQL2005D-01 -Q "EXEC sp_attach_db N'pubs' N'c:\SQL Server 2000 Sample Databases\pubs.mdf'"
There should only be one backslash there.

Not if you're running it from a batch file.

Paul White (3/16/2010)
Is SAM-V01\SQL2005D-01 on the local machine?

Yes.

Paul White (3/16/2010)
sp_attach_db is also deprecated I'm afraid. Use CREATE DATABASE...FOR ATTACH instead.
In fact you might find it easier to attach this sample database from SQL Server Management Studio. In Object Explorer, right-click Databases, and choose Attach...
Paul

I thought my connection problem was due to my usage of sqlcmd from a batch file.

Yesterday, I typed it out on the command prompt, and I had a connection, but I

couldn't get the CREATE DATABASE...FOR ATTACH script to work.

I tried using sqlcmd again this morning, and couldn't get a connection.

I don't know what happened. I have not changed anything with respect to my

connection settings since.

I finally wimped out and used Object Explorer to get the sample databases attached.

I would have liked to have done it with sqlcmd (for my own educational benefit),

but this has taken up too much of my time.

If you (Paul) or anyone else might have an idea as to why I could not get a connection,

I'd be willing to give it a try, but my primary issue has been resolved.

Thanks Paul!

Paul White SSC Guru Points: 150468 More actions · Answer 3

Thanks for the great feedback - especially the thing about slashes in a batch file!

Always frustrating when you don't solve the problem...but hey.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi