Solved

how to execute a SQL statement

Posted on 2013-01-03
11
347 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
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 28

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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now