Solved

how to execute a SQL statement

Posted on 2013-01-03
11
353 Views
Last Modified: 2016-11-23
I am working on a windows XP sp3, DELL optiplex computer.  I have installed some software for a group home, called PPS, which is a database software suite.  It required that I installed SQL server 2005, as part of the install.  The install has been completed successfully.

Next, I moved on to copying the data for the database, from the prior machine/workstation into the appropriate spot.  I was able to correctly copy the files to the correct location, after stopping the SQL services.  I rebooted, and made sure the services started again, as they are set for automatic.

Now, i am left with one last step.  I am being asked to execute a SQL statement that says:  

sp_attach_db 'PPData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData_Log.ldf'

The location of the files has been updated for the new machine, and is accurate.  However, when I try to execute the command from the run prompt it says:

Windows cannot find 'sp_attach_db'.  Make sure you typed the name correctly ...

Can you execute SQL statements from the run command in windows XP.  If not, how would I go about executing this statement so that the database application, PPS, can see the data I have copied in.  I cannot see the data until it has been attached.
0
Comment
Question by:redemption7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 8

Expert Comment

by:mustaccio
ID: 38741051
Try this:
isql -E -d master -Q "exec sp_attach_db 'PPData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData_Log.ldf'"

Open in new window

0
 
LVL 29

Assisted Solution

by:becraig
becraig earned 250 total points
ID: 38741064
Why not do this from the gui

go to Sql server management studio.
connect to the database server
Expand databases
right click on any and attach db then point to your mdf file.

that should work.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38741098
Are you doing it in SQL Server Management Studio? Is SSMS installed. SSMS is a client application to connect to sql server engines and run t-sql query and to perform other tasks as monitoring, adminstration, etc. So, install SSMS and run the command.

You can also use sqlcmdutility for this. However, my recommendation will still be SSMS first.
http://stackoverflow.com/questions/2783862/how-to-attach-a-sql-server-database-from-the-command-line
0
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 13

Expert Comment

by:Ugo Mena
ID: 38741145
Yes you can execute SQL statements from the command line.

The sp_attach_db was deprecated in SQL 2005. To attach your DB use the following:
USE master;
GO
CREATE DATABASE PPData
      ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData.mdf''),
    (FILENAME = C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PPData_Log.ldf')
FOR ATTACH;
GO
     

For more reference: http://msdn.microsoft.com/en-us/library/ms176061%28v=sql.90%29.aspx
0
 

Author Comment

by:redemption7
ID: 38741527
Hey ultralites,

Thank you very much for your help.  Should i put the code you sent into a NotePad file/text file, and then try to run it, or should i try to type that as one big command.  If i do it in NotePad, what should the file extension be ?  Thank you.

Also, as far as GUI software goes, i can only see the sql server configuration manager tool, not the GUI mentioned above.
0
 
LVL 13

Expert Comment

by:Ugo Mena
ID: 38741572
Yes, you should save the code as a script. Then to execute the script use the sqlcmd command from the command prompt.

The format is:

sqlcmd -S [server_name] -U [login_user_name] -P [login_password] -i [full_path_of_sql_file_that_will_be_executed] -o [full_path_of_output_file_after_sql_executed]


For example: to run an SQL file named "script.sql" that is on root drive C and get output results. From the command prompt you would type:

sqlcmd -S yourserver name,1433\MSSQLSERVER -U sa -P password -i C:\script.sql -o C:\output-script.txt

After "script.sql" is executed, there's a new file named "output-script.txt". Check after executing "script.sql" to view the results.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 250 total points
ID: 38743673
You wizard for that. Select MDF file and do it.


Attach DB
0
 
LVL 11

Expert Comment

by:Joseph O'Loughlin
ID: 38751709
Please let us know what's listed in Add and Remove Programs.
Is it MSDE or SQL Express or ...
Not all versions of MS SQL come with TSQL to allow command execution from the command prompt.
0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 38849889
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Closing Comment

by:redemption7
ID: 38849890
Thank you all for your help, and sorry in the delay in closing this out.  I am not often onsite here.  I was able to download and install the management studio software, and use the GUI to attach the database.  I appreciate everyone's help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
Windows 10 Creator Update has just been released and I have it working very well on my laptop. Read below for issues, fixes and ideas.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question