Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to execute a SQL statement

Posted on 2013-01-03
11
Medium Priority
?
356 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Viewers will learn how the fundamental information of how to create a table.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

636 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