January 21, 2014 at 11:50 pm
I'm running SQL Server Express 2008 on a Windows XP laptop. My script file runs okay within the SSMS environment. However, if I run the same .sql file from the command prompt using 'sqlcmd', I get the error below.
SQL Script (A01-Bostwick.sql):
use a_testbed;
Command Line:
sqlcmd -i a01-Bostwick.sql -o a01-Bostwick.lst -e
Error:
Msg 911, Level 16, State 1, Server TBOSTWICK-LT, Line 3 Database 'a_testbed' does not exist. Make sure that the name is entered correctly.
I also ran this successfully so the db exists and I spelled it correctly in the query:
select *
from sys.databases
where name = 'a_testbed';
go
January 22, 2014 at 3:59 am
Does your Windows account has the required permissions on the database? Did you run the code in SSMS using your Windows account or did you use a SQL account?
Can you post the complete script "A01-Bostick01.sql"?
January 22, 2014 at 10:00 am
Hi HanShi: See the complete .sql below. This is homework for a class. How do I determine whether I'm using a Windows account or a SQL account? And where do I check to see what permissions an account has? Thanks for your help.
/* BOSTWICK */
use a_testbed;
GO
/* TASK 00 */
Select getdate();
GO
/* TASK 01 */
Select @@SERVERNAME as ServerName, @@VERSION as VersionNumber;
GO
/* TASK 02 */
delete
from zoo
where z_id > 100;
go
/* TASK 03 */
insert into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
values (101, 'Alphonse', 'Sun Bear', 600.00, '2002-06-15 06:45:00', '2002-05-15');
insert into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
values (102, 'Paddy', 'Red Panda', 1000.00, '2012-06-15 07:45:00', '2012-09-15');
insert into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
values (103, 'Snooze', 'White-faced Saki Monkey', 4000.00, '2008-04-10 08:45:00', '2008-05-15');
go
/* TASK 04 */
Insert Into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
Values(157, 'Bobby','Bear', 7789.00, '2012-05-11 12:45:00','2013-09-15');
Insert Into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
Values(192, 'Lionel', 'Llama', 999.00, '1998-04-11 07:33:00', '2006-07-09');
Insert Into zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
Values(567, 'Fred', 'Frog', 87.00, '2010-04-29 18:30:00', '2010-05-30');
INSERT INTO zoo (z_id, z_name, z_type, z_cost, z_dob, z_acquired)
VALUES
(508, 'Paul', 'Porcupine', 2382.40, '2012-11-09 07:16:30','2013-11-25'),
(113, 'Allan', 'Alpaca', 4850.00, '2009-02-14 20:45:00','2011-03-07'),
(330, 'Gilbert', 'Manatee', 7000.00, '2005-06-21 8:22:00','2008-09-15');
go
/* TASK 05 */
select
z_id, z_name, z_type, z_cost, z_dob, z_acquired
from zoo;
go
/* TASK 06 */
select
z_type, z_name, z_cost
from zoo
order by z_type;
go
/* TASK 07 */
select
z_type, z_name
from zoo
order by z_type, z_name;
go
/* TASK 08 */
select
z_id, z_name, z_dob
from zoo
where z_type = 'Zebra';
go
/* TASK 09 */
select name, type_desc
from sys.objects
where type = 'U';
go
/* TASK 10 */
select
table_schema,
table_name,
column_name,
is_nullable,
data_type
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'zoo';
go
January 23, 2014 at 8:03 am
timSF (1/21/2014)
...My script file runs okay within the SSMS environment...
Did you entered a username/password when you started SSMS and connected to the SQL instance? If so: you're using a SQL account. If not: you use integrated security and are connected with your Windows account.
You're executing the sqlcmd with the "-e" parameter. This indicates you're using integrated security and are connecting with your Windows account.
January 23, 2014 at 8:12 am
The first thing I noticed was that there is no path or drive letter specified for the source or results file. Offhand, I don't know the default path for a SQL Server instance, but any other location would cause the error you encountered.
January 23, 2014 at 8:12 am
timSF (1/22/2014)
...And where do I check to see what permissions an account has?...
Open SSMS and connect to the instance. Expand the "instancename" (which is default allready expanded), "security" and "logins" in the Object explorer pane. Right click the accountname you're using and select properties. See the different pages ("server roles" and "user mappings") for the assigned permissions.
January 23, 2014 at 7:33 pm
TBOSTWICK-LT - is this the right SQL server?
Do you have two (or more) instances of SQL server installed?
Open SSMS and run
select @@Servername
if the result is not the same as TBOSTWICK-LT then note what the result is and add the parameter
-S <the servername>
to your sqlcmd command line.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply