Solved

SQL script not working.

Posted on 2006-11-05
20
710 Views
Last Modified: 2008-03-04
@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create the tables for Study database %DatabaseName%
echo on server %Server%
echo.
pause

sqlcmd -S %Server% -U %User% -P %Password% -d %DatabaseName% -i ..\Database\RG10_Study_Create.sql > %Server%_%DatabaseName%_CreateTables.txt
echo.
echo See %Server%_%DatabaseName%_CreateTables.txt log file.
echo.

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyTables [Database name]
echo.
echo Ex: CreateStudyTables RG_Study_Oct19
echo.
echo ***************************************************

:End


pause



C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_05Nov

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_05Nov
on server sql2005

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See sql2005_RG_StudyMatt_05Nov_CreateTables.txt log file.
0
Comment
Question by:mathieu_cupryk
  • 9
  • 7
  • 4
20 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878408
Have you verified, via client tools, that you can indeed connect to a server called FULLBLOWN using those user credentials?

Why is your script output saying the server is 'FULLBOWN' when inside the script it is defined as SQL2005?

Have you gone to the surface are configuration tool and enabled TCP/IP connections?
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17878450
1) have u enabled sql authentication. and is passowrd correct.
2) have u enabled remote connectoins. right click server-> properties goto connectios page and select remote connections.
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17878452
i tink SQL2005 is the Server Name and the database in it is called FULLBOWN
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878638
In your output, this message here:

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Implies that your script is trying to connect to a server called FULLBLOWN



However this line here:

SET Server=sql2005


Has set the server to be sql2005.




Anyway we'll get back to that once we verify that you have enabled remote connections as per my and Wizilling's posts.

0
 

Author Comment

by:mathieu_cupryk
ID: 17878908
This is how I connect:

Database Engine

FULLBLOWN\SQL2005

SQL Server Authentication

sa

Password123

Is there a way to check the server.

I created the database SQL2005

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878951
There is a way to check. Do you have SQL Sevrer Management studio installed? Start it and verify that you can connect using the parameters that you've just posted.




There is a fair bit of confusion here...

1. This line of your batch (not SQL) script sets the %server% variable to SQL2005:

SET Server=sql2005

2. This line of your output says that actually your %server% variable is FULLBLOWN:

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

3. Your last post said that you need to connect to FULLBLOWN\SQL2005 so in fact neither of the above are correct, you need to be connecting to a server called FULLBLOWN\SQL2005. Therefore this is what you should be setting your variable to and this is what you should be seeing in your output.


4. Lastly, your SQLCMD parameters imply that you are trying to connect to a database that you haven't actually created yet. This won't work either.




Anyway, first verify that you can connect to the database server then we will adress the rest of the issues.
0
 

Author Comment

by:mathieu_cupryk
ID: 17878975
First I have to connect to FULLBLOWN\SQL2005

then there is a database  RG_StudyMatt_05Nov.

C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_05Nov

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateTables.txt log file.

0
 

Author Comment

by:mathieu_cupryk
ID: 17878990
C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_06Nov

About to create a Study database called RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateTables.txt log file.

Press any key to continue . . .

About to create the Procedures for Study database RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateSPs.txt log file.

Press any key to continue . . .
Press any key to continue . . .
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17879044
So in the second one you changed the server to be the correct server name and it connected OK.


Now, this line:

>> The system cannot find the path specified.

Is probably because it can't find ..\Database\RG10_Study_Create.sql

Try using a full path instead of a relative one.

i.e. C:\Folder\Database\RG10_Study_Create.SQL (or whatever is valid) just to get it working.

Then think about the best way to make this path independent.
0
 

Author Comment

by:mathieu_cupryk
ID: 17882114
@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=FULLBLOWN\SQL2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause

Where should I fix this?
0
 

Author Comment

by:mathieu_cupryk
ID: 17882778
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885553
can u check throught sqlcmd.
start a command prompt and type cmd
in the prompt window type
sqlcmd -S FULLBLOWN\SQL2005 -U sa -P Password1
press entere

type a select statement to check if u actuall in. type this
sp_helpdb
go

what does that return

0
 

Author Comment

by:mathieu_cupryk
ID: 17885643
name
                                                 db_size       owner

                                dbid   created     status







            compatibility_level
--------------------------------------------------------------------------------
------------------------------------------------ ------------- -----------------
--------------------------------------------------------------------------------
------------------------------- ------ ----------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------- -------------------
AdventureWorks
                                                     165.94 MB FULLBLOWN\Matt

                                     8 Nov  5 2006 Status=ONLINE, Updateability=
                                     8 Nov  5 2006 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabl
ed, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsA
utoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled



                             90
AdventureWorksDW
                                                      70.50 MB FULLBLOWN\Matt

                                     7 Nov  5 2006 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabl
ed, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsA
utoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled



                             90
master
                                                       4.50 MB sa

                                     1 Apr  8 2003 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateSt
atistics




                             90
model
                                                       1.69 MB sa

                                     3 Apr  8 2003 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Lat
in1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStat
istics

keeps going...

0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885844
ok thats shows that ur connection is ok.


try this
sqlcmd-S FULLBLOWN\SQL2005 -U sa -P Password1 -i CreateDatabase.sql

your problem might be here    > %Server%_%DatabaseName%_CreateDB.txt

%Server% cannot take a \ value

try this:

sqlcmd-S FULLBLOWN\SQL2005 -U sa -P Password1 -i CreateDatabase.sql   > %DatabaseName%_CreateDB.txt
0
 

Author Comment

by:mathieu_cupryk
ID: 17885900
Msg 1801, Level 16, State 3, Server FULLBLOWN\SQL2005, Line 1
Database 'RG_studyMATT_NOV07' already exists.
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885975
good .. try a different database name.. or drop that db if u dont use it.
0
 
LVL 13

Accepted Solution

by:
Wizilling earned 500 total points
ID: 17886007
Oh .. this CreateDatabase.sql script might have the database name hard coded into it.
check and rename as neccessary
0
 

Author Comment

by:mathieu_cupryk
ID: 17886030
This command created the database that was hard coded in CreateDatabase.sql


D:\Voxco.ReportGenerator\Setup>sqlcmd -S FULLBLOWN\SQL2005 -U sa -P Password123
-i CreateDatabase.sql   > %DatabaseName%_CreateDB.txt

Now what should I do about the following:

@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=FullBlown\sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause
0
 

Author Comment

by:mathieu_cupryk
ID: 17886154
I removed %Server%_

and now after it works.

0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17886179
change the line sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
TO --> sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %DatabaseName%_CreateDB.txt

ie remove the %Server% bit in the filenam because its tries to create afilename like FULLBROWN\SQL2005_ db name _ createdb.txt

\ is an illegal character in filename.


or u can use this little modified one.. i added SET ServerAddress = FullBlown_sql2005

@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET ServerAddress =FullBlown_sql2005
SET Server=FullBlown\sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %ServerAddress%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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