?
Solved

how to execute a SQL statement

Posted on 2013-01-03
11
Medium Priority
?
359 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
10 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 1000 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
Industry Leaders: 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!

 
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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This article is about my experience upgrading my consulting machine to Windows 10 Version 1709 (The Fall 2017 Creator Update)
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

807 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